#1 HELP_DAX RANKX Function Strange issue

[_google]

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

Manufacturer	Product	Total Profit	Rank Total Profit by Product SUM	#Rank Total Profit by Product within Manuf
Bower Booms	3 MTA	$142,960.18	1	1
Bower Booms	Aussie Quad	$131,252.83	2	1
Channel Craft	Kangaroo	$135,467.69	1	1
Channel Craft	Omega	$125,677.40	2	1
Channel Craft	V Rang	$82,626.53	3	1
Colorado Boomerangs	Aspen	$288,120.81	1	1
Colorado Boomerangs	Crested Beaut	$225,621.64	2	1
Colorado Boomerangs	Yanaki	$164,658.41	3	1
Gel Boomerangs	Bellen	$273,704.07	2	1
Gel Boomerangs	Quad	$495,972.38	1	1
Gel Boomerangs	Sunshine	$194,931.77	3	1
Manu Rangs	Manu Distance	$208,751.21	1	1
Manu Rangs	Manu MTA	$203,058.35	2	1
Turning Point	Darnell Fast Catch	$361,857.45	1	1
Turning Point	Darnell V	$187,139.27	2	1
Turning Point	Try Fly	$82,962.20	3	1
Grand Total		$3,304,762.19	1

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

Date	Product	Units	Profit	Manufacturer
01/09/2017	Sunshine	29	235.52	Gel Boomerangs
28/02/2017	Yanaki	7	58.68	Colorado Boomerangs
27/10/2017	Darnell Fast Catch	2	32.19	Turning Point
27/09/2017	Try Fly	3	16.32	Turning Point
21/07/2017	Aussie Quad	6	128.45	Bower Booms
06/08/2017	Yanaki	3	25.15	Colorado Boomerangs
08/04/2017	Bellen	2	18.46	Gel Boomerangs
27/05/2017	Yanaki	2	16.77	Colorado Boomerangs
17/09/2017	Quad	12	290.07	Gel Boomerangs
28/11/2017	V Rang	27	104.77	Channel Craft
05/02/2017	Manu Distance	1	72.91	Manu Rangs
23/08/2017	Crested Beaut	34	361.11	Colorado Boomerangs
03/07/2017	Darnell V	3	13.26	Turning Point
10/08/2017	Sunshine	2	16.24	Gel Boomerangs
11/06/2017	Try Fly	3	16.32	Turning Point
05/07/2017	Aussie Quad	3	64.22	Bower Booms
06/09/2017	Darnell V	43	190.12	Turning Point
04/01/2017	Bellen	3	27.69	Gel Boomerangs
18/05/2017	Sunshine	7	56.85	Gel Boomerangs

[Phil_Seamark · Accepted solution]

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

[Phil_Seamark · Accepted solution]

Hi @_google

Did you add these as calculated columns?

Here is a link to a simplified version of the PBIX file with both calculated columns added.

https://1drv.ms/u/s!AtDlC2rep7a-oirt55D4GDKunVSc

[Ashish_Mathur · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

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]

Hi Ashish,

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

regards

[Ashish_Mathur]

You are welcome.

[Phil_Seamark · Accepted solution]

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

[_google]

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

[Phil_Seamark · Accepted solution]

Hi @_google

Did you add these as calculated columns?

Here is a link to a simplified version of the PBIX file with both calculated columns added.

https://1drv.ms/u/s!AtDlC2rep7a-oirt55D4GDKunVSc

[_google · Accepted solution]

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

[_google]

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 

Manufacturer	Product	Total Profit	Rank Total Profit by Product SUM	#Rank Total Profit by Product within Manuf
Bower Booms	3 MTA	$142,960.18	1	1
Bower Booms	Aussie Quad	$131,252.83	2	1
Channel Craft	Kangaroo	$135,467.69	1	1
Channel Craft	Omega	$125,677.40	2	1
Channel Craft	V Rang	$82,626.53	3	1
Colorado Boomerangs	Aspen	$288,120.81	1	1
Colorado Boomerangs	Crested Beaut	$225,621.64	2	1
Colorado Boomerangs	Yanaki	$164,658.41	3	1
Gel Boomerangs	Bellen	$273,704.07	2	1
Gel Boomerangs	Quad	$495,972.38	1	1
Gel Boomerangs	Sunshine	$194,931.77	3	1
Manu Rangs	Manu Distance	$208,751.21	1	1
Manu Rangs	Manu MTA	$203,058.35	2	1
Turning Point	Darnell Fast Catch	$361,857.45	1	1
Turning Point	Darnell V	$187,139.27	2	1
Turning Point	Try Fly	$82,962.20	3	1
Grand Total		$3,304,762.19	1

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

Date	Product	Units	Profit	Manufacturer
01/09/2017	Sunshine	29	235.52	Gel Boomerangs
28/02/2017	Yanaki	7	58.68	Colorado Boomerangs
27/10/2017	Darnell Fast Catch	2	32.19	Turning Point
27/09/2017	Try Fly	3	16.32	Turning Point
21/07/2017	Aussie Quad	6	128.45	Bower Booms
06/08/2017	Yanaki	3	25.15	Colorado Boomerangs
08/04/2017	Bellen	2	18.46	Gel Boomerangs
27/05/2017	Yanaki	2	16.77	Colorado Boomerangs
17/09/2017	Quad	12	290.07	Gel Boomerangs
28/11/2017	V Rang	27	104.77	Channel Craft
05/02/2017	Manu Distance	1	72.91	Manu Rangs
23/08/2017	Crested Beaut	34	361.11	Colorado Boomerangs
03/07/2017	Darnell V	3	13.26	Turning Point
10/08/2017	Sunshine	2	16.24	Gel Boomerangs
11/06/2017	Try Fly	3	16.32	Turning Point
05/07/2017	Aussie Quad	3	64.22	Bower Booms
06/09/2017	Darnell V	43	190.12	Turning Point
04/01/2017	Bellen	3	27.69	Gel Boomerangs
18/05/2017	Sunshine	7	56.85	Gel Boomerangs

[Zubair_Muhammad]

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]

@_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
)