Forum Discussion
Subtotal not Matching Detailed Items
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:
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
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
4 Replies
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.ThanksThank you Tanushree_Kapse, it worked!
- Great!Please Mark this as a solution, if I answered your question. Kudos are always appreciated.Thanks
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!
