Monthly Vs Quarterly

Anything related to PowerPivot and DAX Formuale
arnabmit
Posts: 2
Joined: Thu Sep 03, 2020 5:09 pm

Monthly Vs Quarterly

Postby arnabmit » Fri Sep 11, 2020 12:31 am

Hi,

I have 3 tables, one with sales figures, another a HeadCount table, another a calendar table.

Calendar:
Date Month Quarter
4/1/19 Apr Q1
4/2/19 Apr Q1
4/3/19 Apr Q1
...


SalesPerson HeadCount
Month HC
Apr 12
May 10
Jun 9
Jul 10
...


Sales:
EmpID Date Qty_Sold
1234 4/1/19 3
2345 4/1/19 2
3456 4/1/19 4
...

I want to find out total SumOfQty_Sales/SalesPersons to get AvgQtySoldPerPerson month wise.

The place where I am getting stuck is that when I select the Q1 in the slicer, instead of month.

I want it to give me total Qty_Sold for the quarter / HC for Jun (quarter closing HC). Instead it is giving me total Qty_Sold / total HC.

MattAllington
Posts: 1111
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Monthly Vs Quarterly

Postby MattAllington » Mon Sep 14, 2020 8:40 am

This is a broad data modelling question/problem. There are various ways to fix it. The easiest is simply to use Power Query to change your sales data into monthly data and change your calendar table into a monthly calendar table. That way your data in both tables is the same level of grain as your calendar.

so

Your calendar table will have a single row for each month, plus a unique key for each, such as mmyyyy (you can use what ever you want.
Your headcount table must have the same key mmyyyy, one row per month
Your sales table will have more than 1 row per month, but that doesn't matter. Just make sure it has the same key.

Join both tables to the calendar table using the key
set up your visual using the calendar table to do the aggregation
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

arnabmit
Posts: 2
Joined: Thu Sep 03, 2020 5:09 pm

Re: Monthly Vs Quarterly

Postby arnabmit » Mon Sep 14, 2020 6:52 pm

Hi,

I hope this makes my query a bit easier to understand

This is the representation of the logic I am looking for:
[img]https://drive.google.com/file/d/1Jbgr_sMjuYbYouYbGqGpquKAOfpw7pfr/view?usp=sharing[/img]

This is the slicer I will be working with
[img]https://drive.google.com/file/d/1Af5-VPcIcnmxDgDcZ53dYltgPlM0YTAY/view?usp=sharing[/img]


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: Google [Bot] and 3 guests

cron