Calculating category share by month

[jkaemmerling]

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:

1.  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]))​

[jkaemmerling · Accepted solution]

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. 

[jkaemmerling]

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?

[jkaemmerling]

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? 

[aj1973]

here is the file.

https://drive.google.com/drive/folders/1AZwUoX41Sqyt9AS_j5V_yGgw2udTsO8u?usp=sharing

The chart you sent was not clear to me. try it yourself 

[jkaemmerling]

Thank you very much for your help. Your contribution has sparked the solution I ultimately went with. 

A summary table needs to be created that (as you've shown) contains the device proportions out of each month and device. I thought PowerBI would have done that automatically, but I ended up creating the additional columns required in excel and re-uploading.

I'm sure there's a way to do it in Power BI but I'm not skilled enough. Thank you!

[aj1973]

There is a way to do what in power bi?

Any way glad you made it

[jkaemmerling]

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.

[aj1973]

Hi @jkaemmerling 

Is this good?

[aj1973]

Like this?