Forum Discussion
Matrix Table Calculated Sub-Total
Goo day all.
I have derived Matrix table as below and managed to have total and grand-total working correctly. How to derive total 3 which is the sum of total 2 & 1 and not from calculated balance from ledger balances. i may need to have sub-total from combination of total from Category.
Infromations:
1) Amount are total derived from monthly posted ledger balances as shown in Sub-Category2
2) CPDOC-QS is KPI derived from divinding Amount to Quanity Sold
3) All Totals are standard calucaltion functions from within Matrix table
Hopes my explanation above clear for yoru understanding.
THank you.
6 Replies
Chewbc , Use is in scope and category values
if(not(isinscope(Table[Sub Category]) ) && max(Table[Sub Category]) = "12. Total Cost", [Sub Total Meausre], [Measure])
TO create a measure you can use like
sumx(filter(allselected(Table[Category]), Table[Category] in {"Finance Cost", "Selling and Distribution"} ), [Measure])
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
Thanks mate for the reply. Yes I managed to used sumx to correct my intial problem on Grand-Total from matrix table. WIll try your suggestion and post the results here...
Thank you.
Hi amitchandak I managed to get the results based upon DAX syntax suggested (see below).
The AMOUNT and CPDOC-QS for category "12. TOTAL COST (WO BY PRODUCTS" are sum from category 01. to 11. How to force the value Sub-Total and Sub-Total-CPQS to show under AMOUNT and CPDOC-QS column?
Hi Sir,
The Matrix table above derived from calculated Measures. The measures taken data from posted ledgers and use ledger ac linked to another table structures and calendar.
