Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
The output you are looking for is here >>
This should work for a column
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
The output you are looking for is here >>
This should work for a column
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
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])))
Hi Dom87329,
in your case I would create a calculated column like this:
Then format you calculated column as percantage.
I hope that will help you
_MG_
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
22 | |
20 | |
15 | |
13 |
User | Count |
---|---|
49 | |
41 | |
39 | |
19 | |
19 |