Forum Discussion

Martenandersson's avatar
Martenandersson
Regular Visitor
4 years ago
Solved

Rank Several measures

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?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

6 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    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's avatar
      Martenandersson
      Regular Visitor
      4 years ago

      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

       

       

      CityTest 1Test 2Test 3

      Stockholm

      Yes

      		NoN/A
      GöteborgNoNoYes
      StockholmNoNoNo
      GöteborgYesYesYes
      StockholmYesYesYes
      StockholmYesNoNo

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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