Subtotal not Matching Detailed Items

[dino19547]

 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 

[dino19547]

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!

[dino19547]

Thank you @Tanushree_Kapse, it worked!

[Tanushree_Kapse]

Great!

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

Thanks

[Tanushree_Kapse · Accepted solution]

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