topic Re: Conditional Sum. Need to sum column a per location, then get the MAX column 2 or same data in DAX in DAX Commands and Tips

Conditional Sum. Need to sum column a per location, then get the MAX column 2 or same data in DAX

francesg — Fri, 16 Sep 2022 15:58:57 GMT

Hi,

I need help with the creation of summation specific column using DAX.
I could not think of adding quantity for a specific column; and the cost column should be the same. (sample table below)
I did the "sum" in the column item quantity, but it also sums up the item cost column. So, I put it back to "Don't summarize."

scenario:

Problem:
Warehouse No.	Item	Warehouse Location	Item Quantity	Item Cost
A100	Wheel	Orange	0	200.75
B200	Handle	Red	0	200.75
B200	Handle	Pink	1	200.75
B200	Handle	Blue	1	200.75
B200	Handle	Blue	6	200.75

Expected output in the report should be:

Warehouse No.	Item	Warehouse Location	Item Quantity	Item Cost
A100	Wheel	Orange	0	200.75
B200	Handle	Red	0	200.75
B200	Handle	Pink	1	200.75
B200	Handle	Blue	7	200.75

Appreciate anyone's help.

Thank you

Re: Conditional Sum. Need to sum column a per location, then get the MAX column 2 or same data in DAX

tamerj1 — Fri, 16 Sep 2022 16:18:49 GMT

Hi francesg
You summarze "Item Quantity" by sum and summarize "Item Cost" by max or min

Re: Conditional Sum. Need to sum column a per location, then get the MAX column 2 or same data in DAX

francesg — Fri, 16 Sep 2022 17:27:13 GMT

Hi tamerj1

i tried this measure,

SubTotal ItemQty = MAX('Table_Inventory'[ItemCost], SUM('Table_Inventory'[ItemQty]))

but i get an error:

"A single value for column 'ItemCost' in the table 'Table_Inventory' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count..... "

Appreciate your help

Re: Conditional Sum. Need to sum column a per location, then get the MAX column 2 or same data in DAX

tamerj1 — Fri, 16 Sep 2022 17:36:45 GMT

francesg
This is what I mean. See attached file

Re: Conditional Sum. Need to sum column a per location, then get the MAX column 2 or same data in DAX

francesg — Fri, 16 Sep 2022 17:50:57 GMT

Hitamerj1

Woow it got what i expected output. (i got myself too complicated for the solution) 😀

Thanks so much.