Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a simple table which is getting the better of me 😉. Its here below, called TEST:
Relevancyorder IDProduct groupProductDelivered qtyMinimum qty
Relevant | 562 | 2 | A | 250 | 800 |
Relevant | 562 | 2 | B | 300 | 800 |
Relevant | 562 | 1 | C | 3500 | 4000 |
Not Relevant | 563 | 2 | X | 1500 | 1600 |
Not Relevant | 563 | 1 | C | 3688 | 3000 |
Relevant | 564 | 2 | X | 1000 | 1600 |
Relevant | 564 | 1 | C | 2000 | 3000 |
Relevant | 564 | 2 | B | 150 | 800 |
Relevant | 564 | 2 | A | 700 | 800 |
The table shows 3 ordernumbers selling products from a specific product group. For each line there is a quantity delivered and a required minimum order quantity. One ordernumber is not relevant for this exercise and should be filtered out.
Aim of the game is to drop tolerance of the limit by 3%. I have added a calculated column for that:
called Tolerance qty : Tolerance qty = TEST[Minimum qty]/1.03
Then I need to calculate the minimum order quantity (MOQ) score. This is each time the grouped delivery qty reaches over the max limit of the group. Group limits can only be maxxed not summed. In the example above the order score will depend on delivery quantity sum per group reaching over the group limit for that order. So not the case for order 562.
To calculate the minimum order qty score I created follwing measure:
Solved! Go to Solution.
Hi, @DBNN , you might want to try
MOQ_score =
SUMX (
DISTINCT ( Test[Order ID] ),
VAR __limit =
CALCULATE ( MAX ( Test[Minimum qty] ), Test[Relevancy] = "Relevant" ) / 1.03
VAR __del =
CALCULATE ( SUM ( Test[Delivered qty] ), Test[Relevancy] = "Relevant" )
RETURN
IF ( __limit > 0, ( __del >= __limit ) + 0 )
)
which returns the following result
Apropos, my suggestion on the calculated column [Tolerance qty] = TEST[Minimum qty]/1.03 is that it isn't that necessary given it's logic is fairly simple so that it can apply to a measure on the fly.
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! |
Hi, @DBNN , you might want to try
MOQ_score =
SUMX (
DISTINCT ( Test[Order ID] ),
VAR __limit =
CALCULATE ( MAX ( Test[Minimum qty] ), Test[Relevancy] = "Relevant" ) / 1.03
VAR __del =
CALCULATE ( SUM ( Test[Delivered qty] ), Test[Relevancy] = "Relevant" )
RETURN
IF ( __limit > 0, ( __del >= __limit ) + 0 )
)
which returns the following result
Apropos, my suggestion on the calculated column [Tolerance qty] = TEST[Minimum qty]/1.03 is that it isn't that necessary given it's logic is fairly simple so that it can apply to a measure on the fly.
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! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |