Forum Discussion
Calculating category share by month
Hi there,
I apologize if I misuse terms or am unaware of a feature availabe in DAX/Power BI as I suspect my issue may be additional data prep, but I'm completely stumped. I've linked the data file just in case and I need to show how the proportions of a given category change over time (by month) in a line chart that would show values like below:
Computer Mobile Tablet Other
| July | 47.22% | 49.92% | 2.67% | 0.19% |
| October | 48.81% | 48.66% | 2.36% | 0.17% |
The only way I was able to show this was to use '% of row total' in excel ðŸ˜.
Key points:
- I have a date table and I've created the relationship between dates on the date table and my main dataset.
- My date table is continuous (from 01/2020 to 08/2021), but my main dataset is not
- I have [Date] column in main dataset as Date type, "m/d/yyyy", [Month] as text and [Year] as numeric. [Month] and [Year] are not in use.
- I am not using "show as % of grant total" feature on my visualization.
I've tried three formulas for the calculated measure and they all don't work:
This makes all data points 100%
Count total for Month = DIVIDE(SUM(Combined[Count]),CALCULATE(SUM(Combined[Count]),FILTER('Date','Date'[Date].[Date])))
This gets an MdxScript error, below
Count total for Month = CALCULATE(SUM(Combined[Count]),Combined[Device])/COUNTROWS(Combined)​
Error Message:
MdxScript(Model) (6, 73) Calculation error in measure 'Combined'[Count total for Month]: Cannot convert value 'Computer' of type Text to type True/False.
- This works but does not provide the desired calculation (i.e.- totalling at month)
Count total for Month = SUM(Combined[Count])/CALCULATE(SUM(Combined[Count]),ALLEXCEPT('Date','Date'[Date].[Date]))​
Hi all,
aj1973 was very close with the solution, but a friend and I worked it out to be the following:
DIVIDE(SUM(Combined[Count]),CALCULATE(SUM(Combined[Count]),ALL(Combined[Device])))This can only work when there is a relationship between the date table and main data set ('Combined', in this case), because using the ALL function as a filter, allows you to keep the denominator (total device count in a given month) the same.
10 Replies
- aj1973
Community Champion
- aj1973
Like this?
Hi Amine,
Your calculation is adding all months to 100% - the desired calculation is for each month to be 100% AND for the devices to be xx% of that 100% in each month. Then we can see how each device % changes across each month.
Hi Amine,
Apologies, I'm not doing a good job explaining myself. My goal would for the chart to look like the below but have the %'s add up to 100% for each month.
- aj1973
Is this close?
Yes that's the level of aggregraiton I need - but is it impossible to get it into a line chart?
Or do I have to create a secondary table (like the one you did) in Transform Data to feed these values?
Hi all,
aj1973 was very close with the solution, but a friend and I worked it out to be the following:
DIVIDE(SUM(Combined[Count]),CALCULATE(SUM(Combined[Count]),ALL(Combined[Device])))This can only work when there is a relationship between the date table and main data set ('Combined', in this case), because using the ALL function as a filter, allows you to keep the denominator (total device count in a given month) the same.
