Forum Discussion

GustavoCamargo's avatar
GustavoCamargo
New Member
6 years ago
Solved

DAX - Sum with max and filtered data

Hi everyone,

 

I'm struggling with DAX calculation problem as follows:

 

I have a table similar as mentioned below. I want to find a calculation based on summed maximumn value of 'SERVICE_QTY' times 'NET_PRICE'. I should be able to filter the data by Product Line and Model name.

 

PROD_IDPRODUCT_LINEMODEL_NAMENET_PRICESERVICE_QTYSALES
AAAAPRODUCT AMODEL A259.9220
AAAAPRODUCT AMODEL B259.9320
AAAAPRODUCT AMODEL C259.9220
AAAAPRODUCT AMODEL D259.9220
AAAAPRODUCT BMODEL E259.9220
AAAAPRODUCT BMODEL F259.9320
BBBBPRODUCT AMODEL A300.5415
BBBBPRODUCT AMODEL B300.5315
BBBBPRODUCT AMODEL C300.5115
BBBBPRODUCT AMODEL D300.5115
CCCCPRODUCT BMODEL E350.2210
DDDDPRODUCT BMODEL E259.925
DDDDPRODUCT BMODEL F259.935

 

I have created this measure:

 

QTY_PRICE = 
var MaxQty = 
    CALCULATE(max('Table'[QTY]), ALLEXCEPT('Table', 'Table'[PROD_ID], 'Table'[PRODUCT_LINE], 'Table'[MODEL_NAME]))
Return
    CALCULATE(MaxQty * MAX('Table'[NET_PRICE]), 'Table'[SALES] > 5)

 

 

With that I am able to filter the data and calculation by rows is right, however total sum showed is completly wrong...

In the example, it suppose to be 2936 instead of 1400 (image below)

 

 

Any thoughts?

 

Thanks in advance.

 

1 Reply