% column without creating multiple measures

[Daniel_B]

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

[amitchandak]

To help you further I need pbix file. If possible please share a sample pbix file after removing sensitive information.

[Daniel_B]

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

[Icey]

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.

[Daniel_B]

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 🙂

[Icey · Accepted 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.