Rank Several measures

[Martenandersson]

Hello

I have a excel tabel with 30 columns, in each column the data could be "Yes", "No" or "N/A". I have done measures that is in percent "No" of totals for each column. I now want to see the 4 higest values, depending on filter/slicer. Filter is for example city or region. Can anyone please help me?

[Fowmy]

@Martenandersson 

I think you have created 30 measures one for each column, The ideal approach is to unpivot your data avoid creating so many measures, and make your other calculations easier. 

If you can share a sample or dummy data in Excel or CSV then a solution can be quick.
I you want know how to UnPivot please refer to videos here: https://www.youtube.com/c/ExcelFort

 

[Martenandersson]

Yes, Fowmy. I have done like 30 measure. One for each column. I will check the unpivot in youtube and try that. BUt the next thing is to just show the 4 higest measure of all measures.

Dont know how to attach a sample file here 🙂 But looks like the table below

City	Test 1	Test 2	Test 3
Stockholm	Yes	No	N/A
Göteborg	No	No	Yes
Stockholm	No	No	No
Göteborg	Yes	Yes	Yes
Stockholm	Yes	Yes	Yes
Stockholm	Yes	No	No

[v-yiruan-msft]

Hi @Martenandersson ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Unpivot all of these columns which include the value "Yes" or "No" in Power Query Editor just as suggested by @Fowmy 

2. Create two measures as below to get the percent of "No" value and Rank

percent "No" of totals = 
VAR _numberofno =
    CALCULATE (
        COUNT ( 'Table'[Value] ),
        ALLEXCEPT ( 'Table', 'Table'[Columns] ),
        'Table'[Value] = "No"
    )
VAR _count =
    CALCULATE ( COUNT ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Columns] ) )
RETURN
    DIVIDE ( IF ( ISBLANK ( _numberofno ), 0, _numberofno ), _count, 0 )

Rank = 
RANKX (
    ALLSELECTED ( 'Table' ),
    CALCULATE ( [percent "No" of totals] ),
    ,
    DESC,
    DENSE
)

3. Create the visual and apply the filter with the condition "Rank<=4"

Best Regards

[Martenandersson]

Thanks yingyinr!
Helpt me alot, but i can't still get the visuals to work with filters, out of city, region, date and so on. Do i have to unpivot these columns as well?

[v-yiruan-msft · Accepted solution]

Hi @Martenandersson ，

Please try to update the formula of measure [percent "No" of totals] as below and check whether it can be filtered correctly. You can find the details in the attachment.

percent "No" of totals =

VAR _numberofno =

CALCULATE (

COUNT ( 'Table'[Value] ),

FILTER (

ALLSELECTED ( 'Table' ),

'Table'[Columns] = SELECTEDVALUE ( 'Table'[Columns] )

&& 'Table'[Value] = "No"

)

)

VAR _count =

CALCULATE (

COUNT ( 'Table'[Value] ),

FILTER (

ALLSELECTED ( 'Table' ),

'Table'[Columns] = SELECTEDVALUE ( 'Table'[Columns] )

)

)

RETURN

DIVIDE ( IF ( ISBLANK ( _numberofno ), 0, _numberofno ), _count, 0 )

If the above one is not working, please provide some sample data included with region,date field and your expected result with backend logic and specific examples. Thank you.

Best Regards

[Martenandersson]

Perfect Yinginr! 

It works just fine. Thanks alot!

Another question, if i today (september) get rank 1-4 is like "test 1, test 2 test 3 and test 4".  and want the same rank för last month and the month before to se a trend. How do i do that? Because if i filter by august, the results my be "test 8, test 9, test 10 and test 11", but i want the same as this month, and compare those "tests" 2 month back in time.