Forum Discussion

sjc4062's avatar
sjc4062
Frequent Visitor
8 years ago
Solved

Ranking Different Measures

Hello all,

I am working on what i feel like should be a relatively easy request but i can't wrap my mind around it.

Unfortunately due to the nature of the report i cannot share specific numbers so i will try to explain the best i can and can even draw up some dummy scenarios if it helps.

 

Currently i have a Ranking system based on volume (think sales data), this allows me to make a chart that always shows the top 3 items based on value no matter how much they change. 

This is pretty straight forward and i want to take it a step further.

 

In addtion to the volume data i have some other information related it from differnet sources. With all of this i have 5 different measures all showing different % changes (Think Sales % change, manufacture % change, on time shipping % change,  accounts paid on time % change). What i would like to do is create a visual that always shows the % change that is the biggest. So i need to create some form of ranking system in order to do this i assume.

 

I am not sure where to even begin. Did a lot of googling, tried to play with switch commands as well as nesting max statments and niether seemed to resolve the issue.

 

Any thoughts or things to try?

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi sjc4062,

     

    As of now, the only way I could find is to compare the measures one by one. That case, we will get a long IF statement.

    Measure all =
IF (
    [Measure_1] > [Measure_2],
    IF (
        [Measure_1] > [Measure_3],
        IF (
            [Measure_1] > [Measure_4],
            IF ( [Measure_1] > [Measure_5], [Measure_1], [Measure_5] ),
            IF ( [Measure_4] > [Measure_5], [Measure_4], [Measure_5] )
        ),
        IF (
            [Measure_3] > [Measure_4],
            IF ( [Measure_3] > [Measure_5], [Measure_3], [Measure_5] ),
            IF ( [Measure_4] > [Measure_5], [Measure_4], [Measure_5] )
        )
    ),
    IF (
        [Measure_2] > [Measure_3],
        IF (
            [Measure_2] > [Measure_4],
            IF ( [Measure_2] > [Measure_5], [Measure_2], [Measure_5] ),
            IF ( [Measure_4] > [Measure_5], [Measure_4], [Measure_5] )
        ),
        IF (
            [Measure_3] > [Measure_4],
            IF ( [Measure_3] > [Measure_5], [Measure_3], [Measure_5] ),
            IF ( [Measure_4] > [Measure_5], [Measure_4], [Measure_5] )
        )
    )
)

    Regards,

    Yuliana Gu

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi sjc4062,

     

    As of now, the only way I could find is to compare the measures one by one. That case, we will get a long IF statement.

    Measure all =
IF (
    [Measure_1] > [Measure_2],
    IF (
        [Measure_1] > [Measure_3],
        IF (
            [Measure_1] > [Measure_4],
            IF ( [Measure_1] > [Measure_5], [Measure_1], [Measure_5] ),
            IF ( [Measure_4] > [Measure_5], [Measure_4], [Measure_5] )
        ),
        IF (
            [Measure_3] > [Measure_4],
            IF ( [Measure_3] > [Measure_5], [Measure_3], [Measure_5] ),
            IF ( [Measure_4] > [Measure_5], [Measure_4], [Measure_5] )
        )
    ),
    IF (
        [Measure_2] > [Measure_3],
        IF (
            [Measure_2] > [Measure_4],
            IF ( [Measure_2] > [Measure_5], [Measure_2], [Measure_5] ),
            IF ( [Measure_4] > [Measure_5], [Measure_4], [Measure_5] )
        ),
        IF (
            [Measure_3] > [Measure_4],
            IF ( [Measure_3] > [Measure_5], [Measure_3], [Measure_5] ),
            IF ( [Measure_4] > [Measure_5], [Measure_4], [Measure_5] )
        )
    )
)

    Regards,

    Yuliana Gu