Need Rank based on summarized value instead of Don't Summarize.

[mg01]

Hi Community,

I'm creating a table visual, having columns Unique_id, date, Sales, Quantity, UOM and a measure Unit Price.

Unit Price=  Divide(sum(Sales),sum(quantity))

Now I want to create a Rank Value based on Unit Price Measure, using below measure:

Rank= rankx(allselected('Table',[Unit Price], ,ASC)

When I pull this measure not getting correct value of rank.

When I do, don't summarize for Sales and Quantity in Values Pane , the value comes correct, but I need rank based summarization of Sales and Quantity. How to correct this Rank Value...

Please find the file link attached with this email:
https://drive.google.com/file/d/16huH-TAWp5T0etocUFMOfackCBeGpmce/view?usp=sharing

[Ashish_Mathur]

Hi,

This measure works.  Download the PBI file from here.

Rank 2 = if(isblank([Unit Price]),blank(),RANKX(filter(generate(all('Calendar'[Date]),generate(ALL('Table'[Unique_id]),all('Table'[UOM]))),[Total quantity]<>blank()),[Unit Price],,ASC))

Hope this helps.

[amitchandak]

@mg01 , Rank can change based on context

Lowset level of table

Rank= rankx(allselected('Table') ,[Unit Price], ,ASC)

for product, any other column will give rank of product inside that column

Rank= rankx(allselected('Table'[Product]) ,[Unit Price], ,ASC)

Two columns

Rank= rankx(allselected('Table'[Product], 'Table'[Cagegory]) ,[Unit Price], ,ASC)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

[mg01]

Only single column can be passed under allselected, I tried all option you shared. Can you please tried with shared file and any suggestion at top of it.

[v-yiruan-msft · Accepted solution]

Hi @mg01 ,

I updated your sample pbix file(see the attachment), please check whether that is what you want.

1. Update the formula of measure [Unit Price] as below

Unit Price = 
VAR _seluid =
    SELECTEDVALUE ( 'Table'[Unique_id] )
VAR _seluom =
    SELECTEDVALUE ( 'Table'[UOM] )
VAR _seldate =
    SELECTEDVALUE ( 'Table'[date] )
VAR _sumsales =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Unique_id] = _seluid
                && 'Table'[UOM] = _seluom
                && 'Table'[date] = _seldate
        )
    )
VAR _sumqty =
    CALCULATE (
        SUM ( 'Table'[Quantity] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Unique_id] = _seluid
                && 'Table'[UOM] = _seluom
                && 'Table'[date] = _seldate
        )
    )
RETURN
    DIVIDE ( _sumsales, _sumqty, 0 ) //DIVIDE(sum('Table'[Sales]),sum('Table'[Quantity]))

2. Update the formula of measure [] as below

Rank = RANKX ( ALLSELECTED ( 'Table' ), [Unit Price],, ASC, DENSE )

Best Regards