Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So, % of grand total when used on a line chart does something quite odd compared with other tools i've used. As far as I can see, on my example data below there are 2 "totals" available, ie, the total for the week in question, or the total for the entire period. I'd like the % of grand total to calculate based on the x-axis dimension selected, ie, the numbers it produces are in my "What I want PowerBi to do".
I can see a few posts saying you can achieve this potentially with a stacked column chart, I don't want to use this however. The rational for this is I want to display this on a line chart for 2 main reasons:
See below some example data tables, and some example charts - can I do this? can anyone explain how?
Thanks in advance
Solved! Go to Solution.
Hi @AlexHill
if you cant get the result you want you can always fine tune the calculations with DAX, for your example you could write a measure like this:
Pct of Categories = DIVIDE( CALCULATE( SUM(Data[Value]) ), CALCULATE( SUM(Data[Value]), ALL(Data[Category]) ) )
Hi @AlexHill
if you cant get the result you want you can always fine tune the calculations with DAX, for your example you could write a measure like this:
Pct of Categories = DIVIDE( CALCULATE( SUM(Data[Value]) ), CALCULATE( SUM(Data[Value]), ALL(Data[Category]) ) )
This solution for me is parsing the percentage across the entire chart instead of breaking based on x-axis categories. Is this the intention and/or is it possible to get it to break down by the x-axis categories? For instance in my chart I break it down by group, and am needing to look at what percentage each group has for stories that are completed vs not. I would expect my Stories Completed to be showing 90 some percent for each JK and JR columns.
Example of data:
Table <Monthly Totals>
<Month><Area><Category><NumValue>
Jan JK Stories Completed 8
Jan JK Not on Time 2
Jan JR Stories Completed 6
Jan JR Not on Time 1
Feb JK Stories Completed 30
Feb JK Not on Time 1
Feb JR Stories Completed 15
Feb JR Not on Time 1
Hey @BetterCallFrank, thanks for taking the time to reply. I've managed to get exactly what I needed with your answer. I need to sit down and spend some serious time with DAX formulas at some point, they actually read quite logically once you know what you're looking for but being an excel migrator i'm still getting to grips with them!
Again, massive thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |