Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have the table like that and want to create a report with a RankX
Customer | Material | Order Date | RankX |
A | 123 | 1.1.2019 | 1 |
A | 123 | 1.1.2019 | 2 |
A | 123 | 3.3.2019 | 3 |
A | 145 | 10.1.2019 | 1 |
A | 186 | 5.1.2019 | 1 |
A | 186 | 7.1.2019 | 2 |
B | 123 | 20.1.2019 | 2 |
B | 123 | 9.1.2019 | 1 |
Can somebody help me with that?
Thanks
Christoph
Solved! Go to Solution.
Hi @Anonymous ,
Create a Calculated Column
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hi @Anonymous ,
You may create rank using column or measure like DAX below.
Column: Rankx = RANKX(FILTER(Table, Table[Customer]=EARLIER(Table[Customer])&&Table[Material]=EARLIER(Table[Material])),Table[Order Date],,ASC ,Skip)
Measure: Rankx = RANKX(FILTER(Table, Table[Customer]=MAX(Table[Customer])&&Table[Material]=MAX(Table[Material])), MAX(Table[Order Date]),,ASC ,Skip)
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi all,
both solutions are showing different numbers from what I expected.
Let me rephrase my request.
I have this table:
Format:
Soldto: 123/ABC
Material: 123/ABC
Order date: Whole number (I displayed the dates in the table below in date format)
Customer | Material | Order Date |
A | 123 | 1.1.2019 |
A | 123 | 2.2.2019 |
A | 123 | 3.3.2019 |
A | 145 | 10.1.2019 |
A | 145 | 12.1.2019 |
A | 186 | 7.1.2019 |
B | 123 | 20.1.2019 |
B | 123 | 9.1.2019 |
I want to create a report, which shows me the following.
A ranking (does need to be with formula rank) like this:
I want to see on (Customer & Product) level a ranking of the order dates.
For example
Customer A with Product 123 for order date: 1.1.2019 = 1
Customer A with Product 123 for order date: 2.2.2019 = 2
Customer A with Product 123 for order date: 3.3.2019 = 3
Customer A with Product 145 for order date: 10.1.2019 = 1
Customer A with Product 145 for order date: 12.1.2019 = 2
...
Do you understand what I mean?
BR
Lanko
Hi @Anonymous ,
You can use the below meaures.
power bi is running into performance issues with your nice measure as I want to use that formula in a complexer modell.
To receive a better performance, I want to work with tables instead of reports.
So I created a table with the formula summarize and entered the Columns (Customer, Material, Order Date)
Unfortunately your measure is not working in the table.
Could you help me with that?
Many Thanks
Lanko
Hi @Anonymous ,
Which formula , the rankx formaula or the Var.p formula
Also, please share some sample of the table you have created.
Regards,
Harsh Nathani
Hi @harshnathani,
thanks for the quick reply. I'm talking about the rankx formula.
I created the table: Report_Table
Report_Table = SUMMARIZE(table1;table1[customer];table1[material];table1[Order_date])
Ranking1(calculated column):
Report_table:
Customer | Material | Order_date | Ranking1 |
A | 123 | 1.2.2019 | 1 |
A | 123 | 5.2.2019 | 1 |
A | 123 | 6.3.2019 | 1 |
A | 124 | 1.2.2019 | 1 |
B | 123 | 10.4.2019 | 1 |
B | 123 | 15.6.2019 | 1 |
C | 124 | 3.8.2019 | 1 |
C | 124 | 15.9.2019 | 1 |
So basically the ranking is not working like that.
Do you have an idea on that?
BR
Lanko
Hi @Anonymous ,
Create a Calculated Column
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hi all, Hi Harsh,
Great formula. Any idea how I can achieve this:
Thanx for your help!
Regards, Elmer
Thanks a lot @harshnathani ,
I added measureB sumx (Format:numbers) to the report and now I want to add another MeasureC to the report.
MeasureC
Shall be the variance (var.p) for every "Customer and Material" combination like the rankx.
Unfortunately var.p needs a column and don't work with measureB.
Can somebody help me out here?
Customer | Material | Order Date | MeasureA Rankx (based on your filter formula) | MeasureB Sumx (based on your filter formula) | MeasureC Var.P("on MeasureA") based on |
A | 123 | 1.1.2019 | |||
A | 123 | 10.1.2019 | |||
B | ... | ... | |||
B | |||||
C | |||||
C |
Thanks
lanko
@Anonymous ,
Try
For Rank Refer these links- These are one of the best you can refer
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
Hi @Anonymous ,
Please check if the below measure works.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |