topic Re: Show Values as % of Grand Total in DAX Commands and Tips

Show Values as % of Grand Total

e175429 — Tue, 05 Nov 2024 15:19:04 GMT

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!

Re: Show Values as % of Grand Total

FreemanZ — Tue, 05 Nov 2024 16:10:13 GMT

hi e175429 ,

try like:

% of yearly total =

DIVIDE(

SUM(data[value]),

CALCULATE(

SUM(data[value]),

ALLEXCEPT(data, data[year])

)

Re: Show Values as % of Grand Total

Selva-Salimi — Tue, 05 Nov 2024 16:31:17 GMT

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.

Re: Show Values as % of Grand Total

mark_endicott — Tue, 05 Nov 2024 16:34:57 GMT

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.

Re: Show Values as % of Grand Total

e175429 — Tue, 05 Nov 2024 17:13:35 GMT

I'm not sure what this means in your measure

[count]

Re: Show Values as % of Grand Total

e175429 — Tue, 05 Nov 2024 17:19:00 GMT

Hey Selva,

What do I do with that measure exactly?

Re: Show Values as % of Grand Total

mark_endicott — Tue, 05 Nov 2024 17:25:26 GMT

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

Re: Show Values as % of Grand Total

e175429 — Tue, 05 Nov 2024 17:23:27 GMT

I see.

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

Re: Show Values as % of Grand Total

Selva-Salimi — Tue, 05 Nov 2024 17:28:09 GMT

e175429

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

Re: Show Values as % of Grand Total

mark_endicott — Tue, 05 Nov 2024 17:29:52 GMT

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.

Re: Show Values as % of Grand Total

e175429 — Tue, 05 Nov 2024 17:34:35 GMT

Thank you very much. This worked.

Re: Show Values as % of Grand Total

e175429 — Tue, 05 Nov 2024 17:35:11 GMT

Thank you very much. This worked.

Re: Show Values as % of Grand Total

mark_endicott — Tue, 05 Nov 2024 17:35:29 GMT

e175429 - My pleasure!