cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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:

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]))​``

1 ACCEPTED SOLUTION
Frequent Visitor

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 10
Frequent Visitor

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.
Frequent Visitor

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.

Community Champion

Is this close?

Regards
Amine Jerbi

and you can follow me on

Frequent Visitor

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?

Community Champion

here is the file.

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

Regards
Amine Jerbi

and you can follow me on

Frequent Visitor

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!

Community Champion

There is a way to do what in power bi?

Regards
Amine Jerbi

and you can follow me on

Frequent Visitor

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.

Community Champion

Is this good?

Regards
Amine Jerbi

and you can follow me on

Community Champion

Like this?

Regards
Amine Jerbi

and you can follow me on