% of GRAND TOTAL, KEEPFILTERS on Year

[Dom87326]

Hello, 

I have the following fact table. 

FactTable
Question	AnswerOptions	UserAnswerCount	Year	Custom column or measure
What's your favorite city	Tokyo	12511	2021	21%
What's your favorite city	Tokyo	5922	2022	20%
What's your favorite city	Novosibirsk	13335	2021	23%
What's your favorite city	Novosibirsk	6669	2022	23%
What's your favorite city	Shenzhen	4492	2021	8%
What's your favorite city	Shenzhen	2722	2022	9%
What's your favorite city	Kampala	6590	2021	11%
What's your favorite city	Kampala	3094	2022	11%
What's your favorite city	Minsk	3116	2021	5%
What's your favorite city	Minsk	1393	2022	5%
What's your favorite city	Douala	4167	2021	7%
What's your favorite city	Douala	1959	2022	7%
What's your favorite city	Other	2612	2021	4%
What's your favorite city	Other	2008	2022	7%
What's your favorite city	Wenzhou	11941	2021	20%
What's your favorite city	Wenzhou	5502	2022	19%

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?

Thanks in advance!

[ribisht17 · Accepted solution]

@Dom87326 

The output you are looking for is here >>

This should work for a column 

AnsCount by Year = (Sheet2[ UserAnswerCount])/ CALCULATE(sum(Sheet2[ UserAnswerCount]),FILTER(all(Sheet2),Sheet2[ Year]=EARLIER(Sheet2[ Year])))

OR

AnsCount by Year = (Sheet2[ UserAnswerCount])/ CALCULATE(sum(Sheet2[ UserAnswerCount]),ALLEXCEPT(Sheet2,Sheet2[ Year]))

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 

[_MG_]

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_

[ribisht17]

🙂

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

[tamerj1]

Hi @Dom87326 

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

[_MG_]

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.

I hope that will help you
_MG_