How to show a ranking in a ranking in DAX?

[MarcoW91]

I want to see the top 20 customers [Grp cust name] per [DateYear].[Year] based on [Net Invoiced] How can I show such a ranking per year? I currently have this, but I am unable to manage to get a yearly ranking:

Total = RANKX(InputData,CALCULATE( SUM( [Net Invoiced]), ALLEXCEPT( InputData, InputData[Grp cust name] )),,DESC,DENSE)

Can someone help please?

Thank you kindly!

[tamerj1]

Hi @MarcoW91 
Better to have a Year (intiger data type) column. However, worth to try the following

Total =
RANKX (
    CALCULATETABLE (
        InputData,
        ALLEXCEPT ( InputData, InputData[Grp cust name], InputData[DateYear].[Year] )
    ),
    CALCULATE ( SUM ( [Net Invoiced] ) ),
    ,
    DESC,
    DENSE
)

I guess even the .[Year] part won't be required:

Total =
RANKX (
    CALCULATETABLE (
        InputData,
        ALLEXCEPT ( InputData, InputData[Grp cust name], InputData[DateYear] )
    ),
    CALCULATE ( SUM ( [Net Invoiced] ) ),
    ,
    DESC,
    DENSE
)

[FreemanZ]

hi @MarcoW91 

you would need a year column, then try to add calculated column like:

Total = 
RANKX(
    VALUES(InputData[Year]),
    CALCULATE( SUM( [Net Invoiced]), 
    ALLEXCEPT( InputData, InputData[Grp cust name] )),
    ,
    DESC,
    DENSE
)

[MarcoW91]

Hi FreemanZ, thank you for your answer but I only get 1's out of it:

Total =

RANKX(

    VALUES(InputData[DateYear].[Year]),

    CALCULATE( SUM( [Net Invoiced]),

    ALLEXCEPT( InputData, InputData[Grp cust name] )),

    ,

    DESC,

    DENSE

)

[FreemanZ]

hi @MarcoW91 

are you creating a measure? If so, what context do you have?

[MarcoW91]

I am creating a column with dax as I would need to rename all items which are 20+ to the other category (I am wondering if it is also possible to create another subranking with [Plant ID]

[FreemanZ]

hi @MarcoW91 

try like:

Total = 

RANKX(

    FILTER(

        InputData,

        YEAR(InputData[DateYear]) = YEAR(EARLIER(InputData[DateYear]))

    ),

    CALCULATE( SUM( [Net Invoiced]), 

     ,

    DESC,

    DENSE

)

in case of issue, please consider paste your sample dataset with expected result column.

[MarcoW91]

Unfortunately it doesn't work. Here is the dataset which I modified, the idea is that I always will get the top 19 customers where the others are being classified as other. There are dateyear filters, plant filters and country filters and I would like to show the top 20 based on the filter applied. Would you be able to assist

Calendar Year	Calendar month	Sold-to party	Name	..Groupcustomer	Grp cust name	Country Ship-to-part	..High Mkt. Ship-to	Area	Plant	Company	Material	Product	..SPG-code	Product desc	..SubSubsegment	Product group	Act Invoiced Qty BUM	Act. Weight in KG	Net Invoiced	SGE BU	Act. CON BU	Act. Productcost Grp	Act. Accrd LoadgCost	Act. Accrd PickgCost	Act. Accrued Freight	DateYear
2018	9	23	X	00000000001	X	Netherlands	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Saturday, September 1, 2018
2020	9	23	X	00000000001	X	Netherlands	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Tuesday, September 1, 2020
2020	9	23	X	00000000001	X	Netherlands	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Tuesday, September 1, 2020
2020	9	23	X	00000000001	X	Netherlands	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Tuesday, September 1, 2020
2020	9	23	X	00000000001	X	Netherlands	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Tuesday, September 1, 2020
2020	9	23	X	00000000001	X	Netherlands	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Tuesday, September 1, 2020
2020	9	23	X	00000000001	X	Netherlands	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Tuesday, September 1, 2020
2020	9	23	X	00000000001	X	Netherlands	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Tuesday, September 1, 2020
2020	9	23	X	00000000001	X	Netherlands	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Tuesday, September 1, 2020
2020	9	23	X	00000000001	X	Netherlands	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Tuesday, September 1, 2020
2020	9	23	X	00000000001	X	Netherlands	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Tuesday, September 1, 2020
2020	9	23	X	00000000001	X	Netherlands	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Tuesday, September 1, 2020
2020	9	23	X	00000000001	X	Netherlands	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Tuesday, September 1, 2020
2018	9	23	X	00000000001	X	Netherlands	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Saturday, September 1, 2018
2018	9	23	X	00000000001	X	Netherlands	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Saturday, September 1, 2018
2022	9	23	X	00000000001	X	Netherlands	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Thursday, September 1, 2022
2018	9	34	X	00000000001	X	Germany	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Saturday, September 1, 2018
2018	9	34	X	00000000001	X	Germany	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Saturday, September 1, 2018
2018	9	34	X	00000000001	X	Germany	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Saturday, September 1, 2018
2018	9	34	X	00000000001	X	Germany	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Saturday, September 1, 2018
2022	9	34	X	00000000001	X	Germany	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Thursday, September 1, 2022
2018	9	40	X	00000000001	X	Germany	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Saturday, September 1, 2018
2018	9	40	X	00000000001	X	Germany	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Saturday, September 1, 2018
2018	9	40	X	00000000001	X	Germany	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Saturday, September 1, 2018
2021	9	71	X	00000000001	X	Germany	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Wednesday, September 1, 2021
2021	9	71	X	00000000001	X	Germany	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Wednesday, September 1, 2021
2018	9	71	X	00000000001	X	Germany	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Saturday, September 1, 2018
2018	9	71	X	00000000001	X	Germany	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Saturday, September 1, 2018
2018	9	71	X	00000000001	X	Germany	X	X	X	X	1111111	X	1234	X	NNN	X	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	Saturday, September 1, 2018