DAX Aggregate by multiple fields

datajunkie_29 — Wed, 10 May 2023 01:53:53 GMT

Hello all,

I am fairly new to power bi and have come across a road blocker while creating a measure. I'm trying to aggregate the measure based on several fields from different but related tables.

Dummy Data Model:

Measures Calculated :

Step1-------------

MarginAmt_Y = CALCULATE(SUM(MarginAmt), FILTER(QL_Flag = "Y")

MarginAmt_N = CALCULATE(SUM(MarginAmt), FILTER(QL_Flag = "N")

NetAmt_Y = CALCULATE(SUM(NetAmt), FILTER(QL_Flag = "Y")

NetAmt_Y = CALCULATE(SUM(NetAmt), FILTER(QL_Flag = "N")

QtyAmt_Y = CALCULATE(SUM(Qty), FILTER(QL_Flag = "Y")

Qty_Y = CALCULATE(SUM(Qty), FILTER(QL_Flag = "N")

Step2 ---------------------

Y_Margin/QTY = DIVIDE([NetAmt_Y] - [MarginAmt_Y], [Qty_Y])

N_Margin/QTY = DIVIDE([NetAmt_N] - [MarginAmt_N], [Qty_N])

Step3 -----------------------

FinalMeasure =

IF (

OR([Y_Margin/QTY] = BLANK (), [N_Margin/QTY] = BLANK ()),

IF (

[Y_Margin/QTY] < [N_Margin/QTY],

( ([N_Margin/QTY] - [Y_Margin/QTY]) * [Qty_N]),

)

However, the final measure is returning a zero, as it is calculating at overall level. How do I agrregate it by CustId, BranchId, ItemId

something like:

Measure = SUM(Aggr(

IF (

OR([Y_Margin/QTY] = BLANK (), [N_Margin/QTY] = BLANK ()),

IF (

[Y_Margin/QTY] < [N_Margin/QTY],

( ([N_Margin/QTY] - [Y_Margin/QTY]) * [Qty_N]),

)),BranchId, custid, itemid))

Desired Output:

BranchId	CustId	FinalMeasure
70	1	$ 234.00
65	2	$ 156.00
65	3	$ 786.00
54	1	$ 156.00

Any help is appreciated! Thank you

Re: DAX Aggregate by multiple fields

johnt75 — Wed, 10 May 2023 08:32:12 GMT

You can use something like

My Measure = SUMX ( SUMMARIZE ( Sales, Sales[BranchId], Sales[custid], Sales[itemid] ), IF ( OR ( [Y_Margin/QTY] = BLANK (), [N_Margin/QTY] = BLANK () ), 0, IF ( [Y_Margin/QTY] < [N_Margin/QTY], ( ( [N_Margin/QTY] - [Y_Margin/QTY] ) * [Qty_N] ), 0 ) ) )

Re: DAX Aggregate by multiple fields

datajunkie_29 — Wed, 10 May 2023 14:31:06 GMT

Hi johnt75 , thank you for your response, the solution worked 🙂

topic DAX Aggregate by multiple fields in DAX Commands and Tips

DAX Aggregate by multiple fields

Re: DAX Aggregate by multiple fields

Re: DAX Aggregate by multiple fields