Page 1 of 1

Monthly Vs Quarterly

Posted: Fri Sep 11, 2020 12:31 am
by arnabmit
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.

Re: Monthly Vs Quarterly

Posted: Mon Sep 14, 2020 8:40 am
by MattAllington
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

Re: Monthly Vs Quarterly

Posted: Mon Sep 14, 2020 6:52 pm
by arnabmit
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]