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.
Hi All
I have a column which contains a 1 to 5 score rating against a respective building for performance and I wanted to create a measure that calculates the percent of each score (separate columns is fine) for that respective building but I don't want to create a measure for each as there are 136 different buildings
Is there an easy way to do this as I tried to do a calculation/countrow for the building column and the score column and dividing it by the number of reviews but that didn't work at all
it should be
Total number of 1 score ratings for building / Total Number of Ratings for building = % of 1's for building
Then I can repeat this for each score (2,3,4 and 5)
Thanks for any help on this
Dan_B
Solved! Go to Solution.
Hi @Daniel_B ,
Try this:
Measure =
DIVIDE (
CALCULATE (
COUNT ( 'Table'[Building] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Building] ),
'Table'[Score] = MAX ( 'Table'[Score] )
)
),
CALCULATE (
COUNT ( 'Table'[Building] ),
ALLEXCEPT ( 'Table', 'Table'[Building] )
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
To help you further I need pbix file. If possible please share a sample pbix file after removing sensitive information.
Hi @amitchandak
I am unable to share due to work data safety issues but the columns are simple enough
Column1 Column2 Column3
Date Building 1 1
Date Building 2 5
Date Building 3 4
I've created my own measure which counts the number of rows by building and counts the number of time the score appears as a number 1 but when I try to divide them I get an incorrect %
Thanks for your reply
Dan_B
Hi @Daniel_B ,
Is this what you want?
Measure =
DIVIDE (
CALCULATE (
COUNT ( 'Table'[Building] ),
FILTER ( ALL('Table'), 'Table'[Score] = MAX ( 'Table'[Score] ) )
),
CALCULATE ( COUNT ( 'Table'[Building] ), ALL ( 'Table' ) )
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Icey
Not quite as it's still giving me a different result to my manual calculation in my actual data
What I need to do is find out how many 1s appear next to a single building (building A for example) and work out what percentage that makes up of the total number of reviews against Building A - see below as an example
Building A - 1
Building A - 1
Building A - 4
Building A - 1
Building A - 3
Building A - 2
Building B - 1
Building B - 1
Building B - 1
Building A "1 point scores" = 50%
Building A "2 point scores" = 16.7%
Building A "3 point scores" = 16.7%
Building A "4 point scores" = 16.7%
The 1's for Building B are not included in the total as they would be a different line showing Building B = 100% 1 point scores (3 scores of 1)
Thanks for your help 🙂
Hi @Daniel_B ,
Try this:
Measure =
DIVIDE (
CALCULATE (
COUNT ( 'Table'[Building] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Building] ),
'Table'[Score] = MAX ( 'Table'[Score] )
)
),
CALCULATE (
COUNT ( 'Table'[Building] ),
ALLEXCEPT ( 'Table', 'Table'[Building] )
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
110 | |
109 | |
89 | |
76 | |
66 |
User | Count |
---|---|
126 | |
112 | |
99 | |
82 | |
73 |