topic Subtotal not Matching Detailed Items in DAX Commands and Tips

Subtotal not Matching Detailed Items

dino19547 — Mon, 27 Sep 2021 11:04:29 GMT

Hi PBI community,

I am having issues with subtotals not adding up to total items in a category in a matrix.

My goal is to get the quantities sold for a Product.

For this I need to multiply:

Qantity (Cartons) column in Sales Table x
Qty per Carton column in Product Table

This is my measure formula:

Qty Sold = SUM(Sales[Quantity]) * SUM(Products[Unit])

And this is an example of my two tables above:

Product Table

SKU	Cheese Type	Qty in Carton
Brie01	Brie	10
Brie02	Brie	10
Brie03	Brie	10
Camembert01	Camembert	5

Sales Table

SKU	Qty Sold
Brie01	1
Brie02	1
Brie03	1
Camembert01	3

As evident they are joined by the SKU column which has unique values.

Now, when filtering by "Cheese Type" I am expecting to obtain

30 for Brie (10 x 1) + (10 x 1) + (10 x 1) and
15 for Camembert (5 x 3)

Since Camembert is a unique value at both SKU and Cheese Type level - there are no issues returning the correct amount

However for Brie subtotal - instead of 30, I obtain 90 - which I believe happens because the engine is multiplying by 3 when finding 3 types of Brie cheese products.

What's the most effective way to deal with these type of issues

Re: Subtotal not Matching Detailed Items

Tanushree_Kapse — Mon, 27 Sep 2021 11:29:30 GMT

Hi dino19547 ,

Try using following measure:

Qty Sold2 = SUMX(VALUES('Table (3)'[SKU]),(CALCULATE(SUM('Table (3)'[Qty Sold])* SUM('Table (2)'[Quantity in Carton]))))

Mark this as a solution, if I answered your question. Kudos are always appreciated.

Thanks

Re: Subtotal not Matching Detailed Items

dino19547 — Mon, 27 Sep 2021 11:55:06 GMT

Thank you Tanushree_Kapse, it worked!

Re: Subtotal not Matching Detailed Items

Tanushree_Kapse — Mon, 27 Sep 2021 12:00:28 GMT

Great!

Please Mark this as a solution, if I answered your question. Kudos are always appreciated.

Thanks

Re: Subtotal not Matching Detailed Items

dino19547 — Mon, 25 Oct 2021 09:26:33 GMT

Hi Tanushree, may I ask - why does your solution work with Values(Table[SKU] and not with Values(Table[Cheese Type] under this filter context? Sorry this is a burning question I have!