Forum Discussion

_google's avatar
_google
Helper I
8 years ago
Solved

#1 HELP_DAX RANKX Function Strange issue

Hi I need some help for issue with RANKX function with DAX. (I tried learn it from net, but when i populate in my data table, it does not work. - My data source : I have only one source table ...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    HI _google

     

    Try adding these two calculated columns to your table

     

    Rank of Product = 
    CALCULATE(
        COUNTROWS('fUnits') ,
        FILTER(
            ALL(fUnits) ,
            'fUnits'[Profit] > EARLIER('fUnits'[Profit])
            )
        ) +1
    Rank of Product by Manufacturer = 
    CALCULATE(
        COUNTROWS('fUnits') ,
        FILTER(
            ALL(fUnits) ,
            'fUnits'[Manufacturer] = EARLIER('fUnits'[Manufacturer]) && 
            'fUnits'[Profit] > EARLIER('fUnits'[Profit])
            )
        ) +1
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    You may download my solution from here.  This has been done in MS Excel using the PowerPivot but can also be done in PowerBI desktop.  You may also import all the data and the formulas from this Excel file directly into PowerBI desktop by going to File > Import in PowerBI desktop.

     

    Hope this helps.

     

  • _google's avatar
    _google
    8 years ago

    Hi Phil

     

    Thanks for the solution, it work with calculated column, I was trying to do it through a measure however it helps on learning curve with DAX

     

    regards

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

HI _google

 

Try adding these two calculated columns to your table

 

Rank of Product = 
    CALCULATE(
        COUNTROWS('fUnits') ,
        FILTER(
            ALL(fUnits) ,
            'fUnits'[Profit] > EARLIER('fUnits'[Profit])
            )
        ) +1
Rank of Product by Manufacturer = 
    CALCULATE(
        COUNTROWS('fUnits') ,
        FILTER(
            ALL(fUnits) ,
            'fUnits'[Manufacturer] = EARLIER('fUnits'[Manufacturer]) && 
            'fUnits'[Profit] > EARLIER('fUnits'[Profit])
            )
        ) +1
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

HI _google

 

Try this MEASURE for ranking Total Profit

 

 

Rank Total Profit by Product SUM =
RANKX (
    SUMMARIZE ( ALLSELECTED ( fUnits ), fUnits[Manufacturer], fUnits[Product] ),
    CALCULATE ( SUM ( fUnits[Profit] ) ),
    ,
    DESC,
    DENSE
)

 

 

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    _google

     

    And this MEASURE for ranking Total Profit by Product within each Manufacturer

     

    Rank Total Profit by Product within Manuf =
RANKX (
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( fUnits ),
            fUnits[Manufacturer] = SELECTEDVALUE ( fUnits[Manufacturer] )
        ),
        fUnits[Manufacturer],
        fUnits[Product]
    ),
    CALCULATE ( SUM ( fUnits[Profit] ) ),
    ,
    DESC,
    DENSE
)