March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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:
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
)
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:
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:
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 |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
27 | |
27 | |
21 | |
20 |