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 ，

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.