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 in the model.
- I need Rank of Product in one column and Rank of Product with Material in another column.

Function are

Rank Total Profit by Product SUM:=RANKX(ALL(fUnits[Product]),CALCULATE(SUM(fUnits[Profit])))

Rank Total Profit by Product within Manuf:=IF(HASONEVALUE(fUnits[Product]),RANKX(ALLEXCEPT(fUnits,fUnits[Product]),CALCULATE(SUM(fUnits[Profit]))))



Report view

ManufacturerProductTotal ProfitRank Total Profit by Product SUM#Rank Total Profit by Product within Manuf
Bower Booms3 MTA$142,960.1811
Bower BoomsAussie Quad$131,252.8321
Channel CraftKangaroo$135,467.6911
Channel CraftOmega$125,677.4021
Channel CraftV Rang$82,626.5331
Colorado BoomerangsAspen$288,120.8111
Colorado BoomerangsCrested Beaut$225,621.6421
Colorado BoomerangsYanaki$164,658.4131
Gel BoomerangsBellen$273,704.0721
Gel BoomerangsQuad$495,972.3811
Gel BoomerangsSunshine$194,931.7731
Manu RangsManu Distance$208,751.2111
Manu RangsManu MTA$203,058.3521
Turning PointDarnell Fast Catch$361,857.4511
Turning PointDarnell V$187,139.2721
Turning PointTry Fly$82,962.2031
Grand Total $3,304,762.191 


Data Table Source : This is only sample hence total any different from report view

DateProductUnitsProfitManufacturer
01/09/2017Sunshine29235.52Gel Boomerangs
28/02/2017Yanaki758.68Colorado Boomerangs
27/10/2017Darnell Fast Catch232.19Turning Point
27/09/2017Try Fly316.32Turning Point
21/07/2017Aussie Quad6128.45Bower Booms
06/08/2017Yanaki325.15Colorado Boomerangs
08/04/2017Bellen218.46Gel Boomerangs
27/05/2017Yanaki216.77Colorado Boomerangs
17/09/2017Quad12290.07Gel Boomerangs
28/11/2017V Rang27104.77Channel Craft
05/02/2017Manu Distance172.91Manu Rangs
23/08/2017Crested Beaut34361.11Colorado Boomerangs
03/07/2017Darnell V313.26Turning Point
10/08/2017Sunshine216.24Gel Boomerangs
11/06/2017Try Fly316.32Turning Point
05/07/2017Aussie Quad364.22Bower Booms
06/09/2017Darnell V43190.12Turning Point
04/01/2017Bellen327.69Gel Boomerangs
18/05/2017Sunshine756.85Gel Boomerangs
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

     

10 Replies

  • 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
)
    • _google's avatar
      _google
      Helper I
      8 years ago

      Hi Phil

       

      Thanks for reply, 

       

      it is giving error message 

      Semantic Error :  EARLIER/EARLIEST reference to earlier row context which does not exist.

       

      regards

      _google

      • _google's avatar
        _google
        Helper I
        8 years ago

        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 in the model. 
         - I need Rank of Product in one column and Rank of Product with Material in another column.
                
         Function are  Rank Total Profit by Product SUM:=RANKX(ALL(fUnits[Product]),CALCULATE(SUM(fUnits[Profit]))) Rank Total Profit by Product within Manuf:=IF(HASONEVALUE(fUnits[Product]),RANKX(ALLEXCEPT(fUnits,fUnits[Product]),CALCULATE(SUM(fUnits[Profit]))))



        Report view 

        ManufacturerProductTotal ProfitRank Total Profit by Product SUM#Rank Total Profit by Product within Manuf
        Bower Booms3 MTA$142,960.1811
        Bower BoomsAussie Quad$131,252.8321
        Channel CraftKangaroo$135,467.6911
        Channel CraftOmega$125,677.4021
        Channel CraftV Rang$82,626.5331
        Colorado BoomerangsAspen$288,120.8111
        Colorado BoomerangsCrested Beaut$225,621.6421
        Colorado BoomerangsYanaki$164,658.4131
        Gel BoomerangsBellen$273,704.0721
        Gel BoomerangsQuad$495,972.3811
        Gel BoomerangsSunshine$194,931.7731
        Manu RangsManu Distance$208,751.2111
        Manu RangsManu MTA$203,058.3521
        Turning PointDarnell Fast Catch$361,857.4511
        Turning PointDarnell V$187,139.2721
        Turning PointTry Fly$82,962.2031
        Grand Total $3,304,762.191 


        Data Table Source : This is only sample hence total any different from report view

        DateProductUnitsProfitManufacturer
        01/09/2017Sunshine29235.52Gel Boomerangs
        28/02/2017Yanaki758.68Colorado Boomerangs
        27/10/2017Darnell Fast Catch232.19Turning Point
        27/09/2017Try Fly316.32Turning Point
        21/07/2017Aussie Quad6128.45Bower Booms
        06/08/2017Yanaki325.15Colorado Boomerangs
        08/04/2017Bellen218.46Gel Boomerangs
        27/05/2017Yanaki216.77Colorado Boomerangs
        17/09/2017Quad12290.07Gel Boomerangs
        28/11/2017V Rang27104.77Channel Craft
        05/02/2017Manu Distance172.91Manu Rangs
        23/08/2017Crested Beaut34361.11Colorado Boomerangs
        03/07/2017Darnell V313.26Turning Point
        10/08/2017Sunshine216.24Gel Boomerangs
        11/06/2017Try Fly316.32Turning Point
        05/07/2017Aussie Quad364.22Bower Booms
        06/09/2017Darnell V43190.12Turning Point
        04/01/2017Bellen327.69Gel Boomerangs
        18/05/2017Sunshine756.85Gel Boomerangs



  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    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
      Helper I
      8 years ago

      Hi Ashish,

       

      Thanks for the solution, it works great. As am new to DAX, may i check logical view on using nested RANKX.

       

      regards