Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
MarcoW91
Frequent Visitor

How to show a ranking in a ranking in DAX?

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!
7 REPLIES 7
tamerj1
Super User
Super User

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
Super User
Super User

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
)

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
)

hi @MarcoW91 

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

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]

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.

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 YearCalendar monthSold-to partyName..GroupcustomerGrp cust nameCountry Ship-to-part..High Mkt. Ship-toAreaPlantCompanyMaterialProduct..SPG-codeProduct desc..SubSubsegmentProduct groupAct Invoiced Qty BUMAct. Weight in KGNet InvoicedSGE BUAct. CON BUAct. Productcost GrpAct. Accrd LoadgCostAct. Accrd PickgCostAct. Accrued FreightDateYear
2018923X00000000001XNetherlandsXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Saturday, September 1, 2018
2020923X00000000001XNetherlandsXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Tuesday, September 1, 2020
2020923X00000000001XNetherlandsXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Tuesday, September 1, 2020
2020923X00000000001XNetherlandsXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Tuesday, September 1, 2020
2020923X00000000001XNetherlandsXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Tuesday, September 1, 2020
2020923X00000000001XNetherlandsXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Tuesday, September 1, 2020
2020923X00000000001XNetherlandsXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Tuesday, September 1, 2020
2020923X00000000001XNetherlandsXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Tuesday, September 1, 2020
2020923X00000000001XNetherlandsXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Tuesday, September 1, 2020
2020923X00000000001XNetherlandsXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Tuesday, September 1, 2020
2020923X00000000001XNetherlandsXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Tuesday, September 1, 2020
2020923X00000000001XNetherlandsXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Tuesday, September 1, 2020
2020923X00000000001XNetherlandsXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Tuesday, September 1, 2020
2018923X00000000001XNetherlandsXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Saturday, September 1, 2018
2018923X00000000001XNetherlandsXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Saturday, September 1, 2018
2022923X00000000001XNetherlandsXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Thursday, September 1, 2022
2018934X00000000001XGermanyXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Saturday, September 1, 2018
2018934X00000000001XGermanyXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Saturday, September 1, 2018
2018934X00000000001XGermanyXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Saturday, September 1, 2018
2018934X00000000001XGermanyXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Saturday, September 1, 2018
2022934X00000000001XGermanyXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Thursday, September 1, 2022
2018940X00000000001XGermanyXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Saturday, September 1, 2018
2018940X00000000001XGermanyXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Saturday, September 1, 2018
2018940X00000000001XGermanyXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Saturday, September 1, 2018
2021971X00000000001XGermanyXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Wednesday, September 1, 2021
2021971X00000000001XGermanyXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Wednesday, September 1, 2021
2018971X00000000001XGermanyXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Saturday, September 1, 2018
2018971X00000000001XGermanyXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Saturday, September 1, 2018
2018971X00000000001XGermanyXXXX1111111X1234XNNNX1.001.001.001.001.001.001.001.001.00Saturday, September 1, 2018

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.