topic Re: % of GRAND TOTAL, KEEPFILTERS on Year in DAX Commands and Tips

% of GRAND TOTAL, KEEPFILTERS on Year

Dom87326 — Thu, 09 Jun 2022 10:15:55 GMT

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!

Re: % of GRAND TOTAL, KEEPFILTERS on Year

_MG_ — Thu, 09 Jun 2022 10:59:26 GMT

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_

Re: % of GRAND TOTAL, KEEPFILTERS on Year

tamerj1 — Thu, 09 Jun 2022 11:12:53 GMT

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

Re: % of GRAND TOTAL, KEEPFILTERS on Year

ribisht17 — Thu, 09 Jun 2022 11:52:24 GMT

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

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

Re: % of GRAND TOTAL, KEEPFILTERS on Year

_MG_ — Thu, 09 Jun 2022 12:01:59 GMT

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_

Re: % of GRAND TOTAL, KEEPFILTERS on Year

ribisht17 — Fri, 10 Jun 2022 05:13:29 GMT

🙂

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