Forum Discussion
Helper IIMatrix Table Static Sub Totals
Hi All,
I would like to add a row to matrix table thats shows the total Value$ for each column which ignores any product code number filter selected. So you can compare the sum of selected codes numbers against the total of all the product codes no matter which product codes you select.
All data is in one fact table, there a dimension table for each SK with unique values.
Table name Export_Fact_Codes_All
| calendar_sk | country_sk | loading_sk | discharge_sk | product_code_sk | state_sk | quantity_sk | value$ |
| 300 | 100 | 1 | 600 | 121 | 1 | 400 | 1 328967 |
| 300 | 101 | 1 | 601 | 121 | 2 | 600 | 84353905 |
| 300 | 102 | 2 | 603 | 121 | 2 | 800 | 41839 |
| 301 | 100 | 1 | 600 | 121 | 2 | 900 | 12888454 |
| 301 | 101 | 2 | 601 | 121 | 3 | 1000 | 27880729 |
| 301 | 102 | 2 | 602 | 121 | 3 | 200 | 230237 |
Current Matrix Table
Year Product_Code | 2005 Value$ | 2006 Value$ |
| 121 | ||
| country100 | 1328967 | 12888454 |
| country101 | 84353905 | 27880729 |
| country102 | 41839 | 230237 |
| Total | 85724711 | 40999420 |
| Total_All_Product_Codes | xxxxxxxx | xxxxxxxx |
As per normal behaviour the sub totals changes when I select different product codes. However I would like the sum of the value$ for all products no matter what i select in product_code silcer (Dim_Product_Code), Total_All_Product_Codes.
I've tried a new column in the fact table;
Total = IF(ISINSCOPE(Dim_Product_Code[product_code]),
SUM(Export_Fact_Codes_All[value$]),
CALCULATE(SUM(Export_Fact_Codes_All[value$]),All(Export_Fact_Codes_All)
)
)
Measure;
Total = CALCULATE(SUM(Export_Fact_Codes_All[value$]),All(Export_Fact_Codes_All)
Hopefully this is clear, Thanks for any assistance.
ringovski I hope this helps you. Thank You.
>compare Total Value =CALCULATE([Total Value],ALL('Table A'))
3 Replies
- Mahesh0016
Super User
ringovski Please can you share your Expected results in a table? Thank You.
ringovskiHopefully this is enough information and clear. It's the same as Values 'show on rows' option but only for one measures the Total_All_Product_Codes.
Matrix Table, my expected results is the total sum of all product codes in the last row without any filters being applied as 'Total_All_Product_Codes'.
Year
Product_Code
2005
Value$
2006
Value$
121 country100 1328967 12888454 country101 84353905 27880729 country102 41839 230237 Total 85724711 40999420 Total_All_Product_Codes xxxxxxxx xxxxxxxx I have the expected result in a measure however it appears as column and repeats for every row. So obviously not right.
Total_All_Product_Codes = Calculate(SUM(Export_Fact_Codes_All[value$]),
ALL(Dim_Product_Code)
))
- Mahesh0016
ringovski I hope this helps you. Thank You.
>compare Total Value =CALCULATE([Total Value],ALL('Table A'))