Matrix Table Static Sub Totals

[ringovski]

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.

[Mahesh0016]

@ringovski Please can you share your Expected results in a table? Thank You. 

[ringovski]

Hopefully 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 · Accepted solution]

@ringovski I hope this helps you. Thank You.
> 

compare Total Value =

CALCULATE([Total Value],ALL('Table A'))