cancel
Showing results for
Did you mean:
Helper II

## % of GRAND TOTAL, KEEPFILTERS on Year

Hello,

I have the following fact table.

Could someone help me to write a DAX query for custom column or measure, that computes % of grand total based on UserAnswerCount for each year?

1 ACCEPTED SOLUTION
Super User

The output you are looking for is here >>

This should work for a column

OR

Regards,

Ritesh

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users

5 REPLIES 5
Super User

The output you are looking for is here >>

This should work for a column

OR

Regards,

Ritesh

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users

Frequent Visitor

Hi ritesh,

instead of the earlier-function I would use variables. It makes the code more readable and I think the earlier-function is a bit old fashioned. 😉

Best regards!

_MG_

Super User

🙂

I have given both the options, EARLIER and ALLEXCEPT

True, Variables can improve performance, reliability, readability, and reduce complexity.

although I find it a very straightforward case but I can be wrong, I should respect your opinion

Regards,

Ritesh

Super User

for the grand total: for a calculated column use

CALCULATE ( SUM, ALLEXCEPT ( Table, Table[Year]))

for measure use

CALCULATE ( SUM, REMOVEFILTERS ( Table ), VALUES ( Table[Year])))

Frequent Visitor

Hi Dom87329,
in your case I would create a calculated column like this:

% of total per year =
VAR CurrentYear = Tabelle[Spalte "2"]
VAR CurrentValue = Tabelle[Spalte "3"]
VAR SubTable = Filter(Tabelle, Tabelle[Spalte "2"] = CurrentYear)
return
Divide(Currentvalue, SUMX(SubTable, Tabelle[Spalte "3"]))

Then format you calculated column as percantage.

_MG_