Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dom87326
Helper II
Helper II

% of GRAND TOTAL, KEEPFILTERS on Year

Hello, 

 

I have the following fact table. 

FactTable    
QuestionAnswerOptions 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!

1 ACCEPTED SOLUTION
ribisht17
Super User
Super User

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

ribisht17_0-1654775281896.png

 

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 

 

View solution in original post

5 REPLIES 5
ribisht17
Super User
Super User

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

ribisht17_0-1654775281896.png

 

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 

 

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_

🙂

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
Super User
Super User

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_
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"]))
_MG__0-1654772233690.png


Then format you calculated column as percantage.

_MG__1-1654772298286.png

 


I hope that will help you
_MG_

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors