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 ...
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

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

Martenandersson's avatar
Martenandersson
Regular Visitor
4 years ago

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    • Martenandersson's avatar
      Martenandersson
      Regular Visitor
      4 years ago

      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.