Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to create a Total Sum measure for a matrix?

I am not sure what the correct terminalogy is for this kind of total. The purpose of this Total is to use it to calculate weighted averages.

 

It looks like this:

 

Client/Matter/TranCostTotal Level CostCost Proportion
Client 42300300100%
Matter 10020030066%
M01Transaction15020025%
M01Transaction215020075%
Matter 10110030033%
M02Transaction18510085%
M02Transaction21510015%

 

I am trying to create a measure that keeps a running total depending on what level you are on so i can calculate a proportion. Offices and then Clients" being on the top level, Then Matters, then Transactions. If the measure is at a transaction level it will only total transactions under that matter and use it as a total. If you go up a level to matter it will use all Matter totals as a Total for matters.

 

Currently i have this measure i have built:

 

Total = Calculate(SUM([WIP]),ALLSELECTED(WIPHistory[Transaction_ID]))

 
However the total only works at the very bottom level (matter and is not dynamic). The correct answer is in the above table, however the measure gives me:
Client/Matter/TranCostTotal Level CostCost Proportion
Client 42300300 100%
Matter 100200200 - Incorrect100% incorrect
M01Transaction15020025%
M01Transaction215020075%
Matter 101100100100% %
M02Transaction18510085%
M02Transaction21510015%
 
Basically my measure only handles transactions, i am not sure if it is possible for measures to be dynamic in a mactrix and to Total based off Row groups. 
 
 
Any help would be great
Need Help

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Unfortunetly, the URL linked does not help me in my Total measure.

       

      Its easy to calculatte the total using a all selected. And using a IF condition to check if its filtering correctly so it aggregates correctly in a matrix. The trouble is the dynamic totalling itself.

       

      So say

       

       

      Client-Matter-TranCostMeasure Total
      Client01130160
      Matter0150130
      M01Tran12550
      M01Tran22550
      Matter0280130
      M02Tran18080
      Client0230160
      Matter013030
      M01Tran13030

       

      The custom measure needs to be dependant on the row and parent. So if its transactions row Its the count of all transaction under that matter. Or if its at the matter level its the total of all matter costs under that client.. so on and so forth. So in this case the Total cost of matter 1 normally would be 50. However the custom total measure includes a Total for all Matters under a client01 client. So in this case at the matter level it should be 130 as a total.

       

      any help would be great, i will continue searching and post results here if i figure it out.