Show Values as % of Grand Total

[e175429]

Hello all,

This is my graph, seperated by fiscal year:

For the data labels, instead of whole numbers i would like it to show as % of Grand Total, like this:

 However, BI is summing ALL the values of the whole graph, which is 2165, to get the %'s instead of aumming the years independently, which is giving incorrect %'s.

The total for FY2021 is 66. 

The total for FY2022 is 965

FY 2023 is 1134.

So for "Not at all long" FY2021 should be 71%. 

FY2022 should be 63%. 

So on and so forth.

Attached is the data I am using.

https://docs.google.com/spreadsheets/d/1kQk0gRoMBUQ8qEpx6pfZZIm5FGLmvwfmNLqSxZeivH8/edit?usp=sharing

Help on steps to get what I need would be greatly appreciated!

Thank you!

 

[Selva-Salimi · Accepted solution]

Hi @e175429 

you can write a measure as follows:

Measure =

var _resp =ALLSELECTED(Sheet1[Response])

var _perFY= calculate (count(Sheet1[Response]) , filter (ALLEXCEPT(Sheet1, Sheet1[FY]), sheet1[Response] in _resp))

return count(Sheet1[Response])/_perFY

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.

[mark_endicott · Accepted solution]

@e175429  - Sorry, that should be the original measure you have created, in my test file I have created this measure to define [count]

count =

COUNTROWS( Sheet1 )

So you could actually do this:

% in year = 

Var num = [count]
VAR denom = CALCULATE( [count], ALL( Sheet1[Response]))

RETURN DIVIDE( num, denom )

Basically I saying that you should create two different measures. The measure [count] is a replacement for where you are using a count of your "How long was....." column

[Selva-Salimi · Accepted solution]

@e175429 

add the measure to y-axis! and also you can add it to tooltip

[mark_endicott]

@e175429 - You can use the following DAX in a measure:

% in year = 

VAR num = COUNTROWS( Sheet1 )
VAR denom = CALCULATE( [count], ALL( Sheet1[Response]))

RETURN DIVIDE( num, denom )

This respects the filter of year, quarter or question, in your small multiples but for the denominator it removes the filter of Response. 

If this works for you, please accept it as the solution, to help for visibility for others. 

[e175429]

I'm not sure what this means in your measure

[count]

[mark_endicott · Accepted solution]

@e175429  - Sorry, that should be the original measure you have created, in my test file I have created this measure to define [count]

count =

COUNTROWS( Sheet1 )

So you could actually do this:

% in year = 

Var num = [count]
VAR denom = CALCULATE( [count], ALL( Sheet1[Response]))

RETURN DIVIDE( num, denom )

Basically I saying that you should create two different measures. The measure [count] is a replacement for where you are using a count of your "How long was....." column

[e175429]

Thank you very much. This worked.

[mark_endicott]

@e175429 - My pleasure!

[e175429]

I see.

Where do I put that measure? How do I apply it to the graph?

[mark_endicott · Accepted solution]

@e175429 - if you only want to see the % values, you can leave it out, it will just be referenced automatically when the % measure is invoked. 

It's what is known as measure branching.

To apply my % of year measure, take out the count of your "How long was....." column and add the new measure to the Y-axis.

[Selva-Salimi · Accepted solution]

Hi @e175429 

you can write a measure as follows:

Measure =

var _resp =ALLSELECTED(Sheet1[Response])

var _perFY= calculate (count(Sheet1[Response]) , filter (ALLEXCEPT(Sheet1, Sheet1[FY]), sheet1[Response] in _resp))

return count(Sheet1[Response])/_perFY

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.

[e175429]

Thank you very much. This worked.

[e175429]

Hey Selva,

What do I do with that measure exactly?

[Selva-Salimi · Accepted solution]

@e175429 

add the measure to y-axis! and also you can add it to tooltip

[FreemanZ]

hi @e175429 ,

try like:

% of yearly total =

DIVIDE(

    SUM(data[value]),

    CALCULATE(

         SUM(data[value]),

         ALLEXCEPT(data, data[year])

    )

)