topic Re: Calculated Column: Sum of another column from same table based on criteria in DAX Commands and Tips

Calculated Column: Sum of another column from same table based on criteria

rh203 — Tue, 29 Sep 2020 10:51:16 GMT

Hi,

I am trying to sum a value in a table to create a pallet projection based on a starting stock position, inbound and outbound volume.

All of this data is within the same table but when I try to use a calculate function to bring back a value within the same table based on a couple of conditions, the formula simply brings back the total value from the column I am summing, rather than the specific sum of only the row criteria (product code).

Difficult to explain but the intention is to create a pallt projectio at product level which I can then roll up to various levels of aggregation.

To cut a long story short, it needs to be done like this in a calculated column format as the projection itself needs to be worked out in PBI itself during each refresh)

Thanks for any help

Re: Calculated Column: Sum of another column from same table based on criteria

HotChilli — Tue, 29 Sep 2020 12:30:51 GMT

I think we'll need more information.

It's a little difficult to decipher the picture posted because all the scoring out and zero values.

Are you trying to get the sum of the 'Pallet Count Latest' value for all the rows that have the same Datekey Index and Factory Code as the current row?

Re: Calculated Column: Sum of another column from same table based on criteria

rh203 — Tue, 29 Sep 2020 12:33:31 GMT

Hi, Thanks for the reply - yes sorry I had to redact for obvious reasons.

I worked out the solution in the last 15 mins - see below.

Looks like 'EARLIER' is key to pick up the current row as the criteria for a filter within the CALCULATE function.

It's done the job.

Thanks for your help

Re: Calculated Column: Sum of another column from same table based on criteria

HotChilli — Tue, 29 Sep 2020 13:04:42 GMT

OK, you're on your way.

You could improve it by amalgamating the two FILTER clauses into one.

FILTER(Pallet_projection, Pallet_projection[dateke...] = 0 && Pallet_projection[factor...] = EARLIER Pallet_projection[factor...]

I've just shortened my typing with .......