SUMX and MAXX for grouped results

[DBNN]

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:

MOQ score =

var _MOQlimit = calculate(maxx(TEST,TEST[Tolerance qty]),filter(TEST,TEST[Relevancy]="Relevant"))

var _MOQdel = calculate(sumx(TEST,TEST[Delivered qty]),filter(TEST,TEST[Relevancy]="Relevant"))

RETURN

calculate( sumx(TEST,IF(_MOQlimit>_MOQdel,0,1)),filter(TEST,TEST[Relevancy]="Relevant"))

That seems to look ok except for the total showing me per order a crazy result for the 2 relevant orders:

562 gets a 300% score and 564 a 400%.    

Can anybody correctly calculate the MOQ score per order? and also the correct total score for all orders ?  

Would be great !   Thanks for help

[CNENFRNL · Accepted 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.