Ranking Measure

[Anonymous]

Everyone, I need your help again. I am struggling with creating a measure to rank manufacturers withing a category. I am attaching the sample data and desired result, as well as data model screenshot.

"OOW / Direct" is a calculated column in the table "DATA". "Manufacturer" is a column in another table. As you can see below the context is changed by two filters - "Month" and "CC ID".

Any ideas?

[jdbuchanan71]

Hello @Anonymous 

I uploaded my sample .pbix file here for you to look at.  MFR_Ranking.pbix

Give this measure a try:

Ranking = 
VAR OOW_DIR = VALUES ( Data[OOW / DIR] )
RETURN 
IF ( NOT ISBLANK ( [Sales Amount] ),
    IF ( ISINSCOPE ( MANUFACTURER[MFGRID] ),
        CALCULATE( 
            RANKX ( 
                ALLSELECTED ( MANUFACTURER ), 
                [Sales Amount] ), 
            OOW_DIR
        ) 
    )
)

[Anonymous]

Thank you for such a quick reply.

Any chance to re-write the measure without using "ISINSCOPE" function?

I am limited to an older version of Power BI / DAX 😞

Long story

[jdbuchanan71 · Accepted solution]

Can you use ISFILTERED?

Ranking = 
VAR OOW_DIR = VALUES ( Data[OOW / DIR] )
RETURN 
IF ( NOT ISBLANK ( [Sales Amount] ),
    IF ( ISFILTERED ( MANUFACTURER[MFGRID] ),
        CALCULATE( 
            RANKX ( 
                ALLSELECTED ( MANUFACTURER ), 
                [Sales Amount] ), 
            OOW_DIR
        ) 
    )
)

[Anonymous]

Yes, it works like a charm.

I checked the first solution at home too with an updated version of Power BI --> works!

Thank you again.