Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello! I could use some help with getting a measure to provide a percent of text values of a column but needs determine if it equals another measure. For instance if YES count equals per location then "100%" but if NO equals per location then "0%". I need a measure that is the percent of yes & no in a single column (percent of grand total won't work).
Current Measures:
Countperlocation = CALCULATE ( DISTINCTCOUNT('Table'[ID Number] ), ALLEXCEPT ( 'Table','Table'[Location]) )
Count_YES_Location = CALCULATE( COUNTROWS('Table' ), 'Table'[(Result)] = "YES" , ALLEXCEPT('Table','Table'[Location]))
Count_NO_Location = CALCULATE( COUNTROWS('Table' ), 'Table'[(Result)] = "NO" , ALLEXCEPT('Table','Table'[Location]))
Desired Ouput:
Location | Count per location | Result = YES | Result = NO | PercentStatus |
Location 1 | 8 | 8 | 100% | |
Location 2 | 1 | 1 | 100% | |
Location 3 | 2 | 2 | 100% | |
Location 4 | 5 | 5 | 100% | |
Location 5 | 9 | 9 | 100% | |
Location 6 | 14 | 14 | 100% | |
Location 7 | 4 | 4 | 100% | |
Location 8 | 16 | 16 | 0% | |
Location 9 | 16 | 16 | 0% | |
Location 10 | 16 | 16 | 0% | |
Location 11 | 27 | 27 | 0% | |
Location 12 | 27 | 27 | 0% | |
Location 13 | 27 | 27 | 0% | |
Location 14 | 26 | 1 | 25 | 10% |
Location 15 | 27 | 27 | 0% | |
Location 16 | 29 | 3 | 26 | 30% |
Solved! Go to Solution.
@Anonymous there was typo
PercentStatus =
VAR __countPerLocation = [Countperlocation]
RETURN
SWITCH ( TRUE(),
__countPerLocation = [Count_YES_Location], 1,
__countPerLocation = [Count_NO_Location], 0,
DIVIDE([DistinctCountResults],[CountperLocation])
)
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous there was typo
PercentStatus =
VAR __countPerLocation = [Countperlocation]
RETURN
SWITCH ( TRUE(),
__countPerLocation = [Count_YES_Location], 1,
__countPerLocation = [Count_NO_Location], 0,
DIVIDE([DistinctCountResults],[CountperLocation])
)
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous try this measure:
% =
VAR __countPerLocation = [Counterperlocation]
RETURN
SWITCH ( TRUE(),
__countPerLocation = [Count_Yes_Location] = 1,
__countPerLocation = [Count_No_Location] = 0,
<<your % formula>
)
and change the format of this new measure to %.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hello thanks for the response! I did try your measure but I am getting an error 😕
Error Message:
MdxScript(Model) (34, 3) Calculation error in measure 'Table'[PercentStatus]: DAX comparison operations do not support comparing values of type True/False with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.
New Measure:
PercentStatus =
VAR __countPerLocation = [Countperlocation]
RETURN
SWITCH ( TRUE(),
__countPerLocation = [Count_YES_Location] = 1,
__countPerLocation = [Count_NO_Location] = 0,
DIVIDE([DistinctCountResults],[CountperLocation])
)
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
64 |