Forum Discussion
Column level totals for multiple values in each row
Hi, I am using a matrix to display some data. I have one column for months, rows for parent category and sub category and values for quantity of item, price of item and a measure for sum(quantit...
CoreyP
Solution Sage
Solution SageTry this:
IF(ISFILTERED('Table'[Parent Category Name]) , [Qty]*[Price per Item] , SUMX('Table', [Qty]*[Price per Item] ))
Thanks CoreyP but unfortunately I can't get that working.
The Table structure the matrix uses is as follows;
Category
| Id | Name | ParentCategoryName (Self Referencing to Category Id) |
Product
| Id | Category Id | Name | Price Per Item |
Amount
| Id | Product Id | Date Wanted (Month) | Quantity |
Thanks
H
- CoreyP
Yeah, you'd just need to edit it with your specific table names.. so something more like this:
IF( ISFILTERED('Category'[ParentCategoryName]), SUM('Amount'[Quantity]) * SUM('Product'[Price Per Item]) , SUMX('Category' , SUM('Amount'[Quantity]) * SUM('Product'[Price Per Item]) )
This is basically saying, if the measure is in the context of a parent category name, do one operation, and when it's outside of that context, aka the total line, do a sum of the measure.