Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AlexHill
Regular Visitor

% of x-axis dimension rather than grand total

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:

  • In my experience a line chart is the best visualisation for displaying data over period of time, I dislike column charts for this purpose as it forces the end user to try and work out the slant between 2 seperate data points
  • Stacked charts, particularly as you add more and more Categories are very difficult for users to see the difference within specific Categories when they are in the middle of the column (obv, top/bottom category you can see a trend in

See below some example data tables, and some example charts - can I do this? can anyone explain how?

 

Thanks in advance

 

tables.jpgCharts.jpg

 

1 ACCEPTED SOLUTION
BetterCallFrank
Resolver IV
Resolver IV

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

View solution in original post

3 REPLIES 3
BetterCallFrank
Resolver IV
Resolver IV

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

 

 

 

 

 

I would like to see the % be the 90 some percentI would like to see the % be the 90 some percent

 

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.