Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm trying to perform an analysis in which I can see the biggest understocks and overstocks in a Pareto chart for a selected date. To achieve this, I need a cumulative sum of all volumes, taking on account the biggest gap/excess in the referred day.
My data is being displayed as below. The headers in grey are already available; the red and yellow headers are those that I'm striving to obtain. Despite being shown as columns, they should be measures.
Product | Day | Understock | UndRank | CumUnd | CumUnd% | Overstock | OveRank | CumOve | CumOve% |
01234567AB | 01/01/2020 | 80 | 2 | 190 | 76% | ||||
12345678BC | 01/01/2020 | 50 | 3 | 240 | 96% | ||||
23456789CD | 01/01/2020 | 110 | 1 | 110 | 44% | ||||
34567890DE | 01/01/2020 | 55 | 2 | 115 | 100% | ||||
45678901EF | 01/01/2020 | 10 | 4 | 250 | 100% | ||||
56789012FG | 01/01/2020 | 60 | 1 | 60 | 52% | ||||
01234567AB | 02/01/2020 | 90 | 1 | 90 | 42% | ||||
12345678BC | 02/01/2020 | 45 | 3 | 215 | 100% | ||||
23456789CD | 02/01/2020 | 80 | 2 | 170 | 79% | ||||
34567890DE | 02/01/2020 | 50 | 2 | 105 | 91% | ||||
45678901EF | 02/01/2020 | 10 | 3 | 115 | 100% | ||||
56789012FG | 02/01/2020 | 55 | 1 | 55 | 48% |
So far, I was had worked only with the latest date, so the formulas below had been enough. I tried a lot of different solutions, but with no success. If the MAX formula considers the maximum value for the day shown in the row, I believe it will be enough.
CumUnd = CALCULATE(SUM(Table[Understock]), FILTER(ALLSELECTED(Table), Table[UndRank] <= MAX(Pareto[UndRank])))
Hi, @Anonymous , you might want to refer to the attached file for details.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks a lot, @CNENFRNL!
It works perfectly! Now I'll go a little deeper in the formula to understand the logic behind it.
Kind regards!
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |