How to create a dax measure which is a product of 2 different measure with duplicate dimensions

[TusharGaurav]

Hi Experts,

I have the following sample data:

Order Id	Line Id	Quantity	Unit Price
12345	1	4	10
12345	2	3	11
54321	1	8	4
54321	2	5	9
54321	2	7	0

I need to create Revenue where Revenue = Quantity * Unit Price

In case of Unit Price = 0, Revenue will be equal to product of sum of quantity and non zero "Unit Price" for the corresponding "Order Id" and "Line Id".

In my report I need to show only "Order Id" ,"Quantity" and "Revenue", the data should be as below in "Table" visual:

Order Id	Quantity	Revenue
12345	7	73
54321	20	140

Calculation needed for  "Order Id": 12345 = (4*10) +(3*11)= 73

Calculation needed for  "Order Id": 54321 = (8*4) +((5+7)*9)= 140

Here for "Order Id": 54321, as shown above,Revenue will be equal to product of sum of quantity and non zero "Unit Price" for the corresponding "Line Id".

Can you please suggest how we can achieve this requirement.

[rajendraongole1 · Accepted solution]

Hi @TusharGaurav - create a DAX measure that handles the logic to sum quantities and apply the non-zero unit price when necessary.

you can replace with your table name:

TotalQuantity =

SUM('sdm'[Quantity])

create revenue measure as below:

Revenue =

SUMX(

    SUMMARIZE(

        'SDM',

        'SDM'[Order Id],

        'SDM'[Line Id],

        "RevenueForLine",

        IF(

            MAX('SDM'[Unit Price]) = 0,

            SUM('SDM'[Quantity]) * CALCULATE(MAX('SDM'[Unit Price]), 'SDM'[Unit Price] > 0),

            SUM('SDM'[Quantity]) * MAX('SDM'[Unit Price])

        )

    ),

    [RevenueForLine]

)

Ouput:

Hope this works

[TusharGaurav]

Hi Rajendra,

Thanks a lot for your help.

It worked 🙂 

Thanks and Regards,

Tushar Gaurav