topic Re: Calculate SUM with two filters in DAX Commands and Tips

Calculate SUM with two filters

Lunden — Tue, 19 Apr 2022 11:12:17 GMT

Hi I really need some help,

I need to calculate a sum for Column Comp.Qty (BUn) in a newcolumn (SUM BUn All BareAxles) if column Bare Axles and column Component contain the same value

This it what I have this far:

SUM BUn All BareAxles = CALCULATE(SUM(BareAxleAll[Comp. Qty (BUn)]),

FILTER (BareAxleAll, BareAxleAll[Component number] = EARLIER (BareAxleAll[Component number]

)))

Thanks Maria

Re: Calculate SUM with two filters

SpartaBI — Tue, 19 Apr 2022 11:55:50 GMT

Hey Maria,
Not sure why you want this in a column and not a measure.
Can you show me what number do you want it to be in each row in this sample table:

Than I will write back the formula

Re: Calculate SUM with two filters

Lunden — Tue, 19 Apr 2022 12:06:33 GMT

Bare Axles	Component	Comp. Qty (Bun)	SUM Bun All BareAxles
A	BA	10	20
A	CA	10	10
B	BA	10	10
C	AA	10	10
A	BA	10	20
C	CA	10	10

Re: Calculate SUM with two filters

SpartaBI — Mon, 09 May 2022 19:39:47 GMT

Are you sure?? 🙂
Why is there 2 letters in the 2nd column?

Not sure yet what you want, but try this:

SUM BUn All BareAxle =

IF(

'Table'[Bare Axels] = 'Table'[Component],

SUMX(

FILTER(

'Table',

'Table'[Bare Axels] = 'Table'[Component]

'Table'[Comp. Qty (BUn)]

)

Re: Calculate SUM with two filters

Lunden — Tue, 19 Apr 2022 12:55:12 GMT

Hi,

The colum "Bare Axle" haved 8 different part numbers, (level 1) this is the main part number for a complete unit. The 8 different Bare Axles then contains of 200-300 different parts who are in column "components", some are in every bare axle, and might also occur severel times in the column bare axle with the same partnumber and component number. The column "Comp. Qty (BUn)" tells how many components needed for each row in the structure for the Bare Axle

I need the total sum ("Comp. Qty (BUn)") of each component number in column "component" for every bare axle,

I'm not sure i managed to explain so it is easy for you to understand, please ask if it is unclear for youu if i can explain differently

Great thanks Maria

Re: Calculate SUM with two filters

Lunden — Tue, 19 Apr 2022 13:04:00 GMT

Maby it would be better do do a mesure for this?

😉

Re: Calculate SUM with two filters

SpartaBI — Tue, 19 Apr 2022 13:37:22 GMT

For sure. Unless you need it for slice and dice in visuals, and only need it as value to show, 99% would be better to do in a measure.
I still don't understand your data 😞
Could you maybe share a sampe of the data here so I could copy it easily and create the measure.
Put the same column names etc so you could just copy paste the code I will (hopefully haha) send

Re: Calculate SUM with two filters

Lunden — Tue, 19 Apr 2022 14:18:16 GMT

As you can see here, I get the total sum "8" for all Bare Axle (first column), I would like to get the sum of "Comp. Qty (BUn)" for each Bare axle = 1 in the case in the pictu

re, i will use the result for a calculation to prognose the need of each component depending on volume on each bare axle

Re: Calculate SUM with two filters

Anonymous — Tue, 26 Apr 2022 06:41:15 GMT

Hi Lunden,

Any update for these? Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng