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.
I'm sure I'm missing something obvious, but I just can't figure it out.
My company wants to visualize scores on an audit. The score is a percentage, just like a grade a test. The score is calculated as:
#Yes/(#Yes + #No)
So, 20 yes, 10 no would be
20/(20+10)= 66%
When I try to visualize this, I can only get a 1 because the column graph wants to do a Count of Total.
What am I missing??
Solved! Go to Solution.
Ok, so I found a solution.
First, I went back to my query and realized that the total column was giving me "NaN" when the denominator was 0. To resolve this, I updated the formula for this column to
= if ([Yes]+[No]=0) then 0 else ([Yes]/([Yes]+[No]))
This resolved the NaN issue and gave me all numbers
Next, I changed the column type to Percentage
After reloading the queary, the visualization returned the expected results, despite indicating that it was still summing the values.
I did find one limitation. Because I have mulitiple locations for this data, I only get these "clean" results when I am filtered for a single location. If I try to combine the scores across locations, I get all of the locations added together. This makes sense, so I changed the function to Average and that resolved the problem. Reverting to a single location still returned the correct values for that individual location.
Ok, so I found a solution.
First, I went back to my query and realized that the total column was giving me "NaN" when the denominator was 0. To resolve this, I updated the formula for this column to
= if ([Yes]+[No]=0) then 0 else ([Yes]/([Yes]+[No]))
This resolved the NaN issue and gave me all numbers
Next, I changed the column type to Percentage
After reloading the queary, the visualization returned the expected results, despite indicating that it was still summing the values.
I did find one limitation. Because I have mulitiple locations for this data, I only get these "clean" results when I am filtered for a single location. If I try to combine the scores across locations, I get all of the locations added together. This makes sense, so I changed the function to Average and that resolved the problem. Reverting to a single location still returned the correct values for that individual location.
Thank you everyone for the responses.
First, the data is in 3 columns
Yes - No - N/A
The "Total Score" column is a calculated column that I added in Power Query
My desired output would be something like this (from existing Excel)
Can you share the sample data or smaple output screenshot?
Thanks,
try this measure,
Score% =
DIVIDE(MAX('Yes/No'[Yes]),SUMX('Yes/No','Yes/No'[Yes]+'Yes/No'[No]))
Thanks,
Hi Anna,
Here you using max .It consider only Maximum Value in Column or Consider sum of Total value 😊😍
For the above, we need max of that particular context if it is having one row like the above (For eg: A is having one row and we are considering MAX of that context) and summing up two column values of that row for both yes and no, so it will work in this scenario.
Thanks,
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
86 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |