How to create a Total Sum measure for a matrix?

[Anonymous]

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/Tran	Cost	Total Level Cost	Cost Proportion
Client 42	300	300	100%
Matter 100	200	300	66%
M01Transaction1	50	200	25%
M01Transaction2	150	200	75%
Matter 101	100	300	33%
M02Transaction1	85	100	85%
M02Transaction2	15	100	15%

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/Tran	Cost	Total Level Cost	Cost Proportion
Client 42	300	300	100%
Matter 100	200	200 - Incorrect	100% incorrect
M01Transaction1	50	200	25%
M01Transaction2	150	200	75%
Matter 101	100	100	100% %
M02Transaction1	85	100	85%
M02Transaction2	15	100	15%

 

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

[rajulshah]

Hello @Anonymous,

Please refer following link. Maybe it could help.
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=619c7481-3849-49d1-abbe-bab6efeaac0d&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer

Or else please provide sample raw data or power bi file.

[Anonymous]

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-Tran	Cost	Measure Total
Client01	130	160
Matter01	50	130
M01Tran1	25	50
M01Tran2	25	50
Matter02	80	130
M02Tran1	80	80
Client02	30	160
Matter01	30	30
M01Tran1	30	30

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.

[ImkeF · Accepted solution]

Hi @Anonymous ,

the ISINSCOPE-function is your friend for this task: https://xxlbi.com/blog/new-dax-function-isinscope/ 

[Anonymous]

Thank you! its a good lead, its a shame its not dynamic but it will have to do.Thanks!

[ImkeF]

Hi

not sure I understand what you mean with "not dynamic". But you can define all levels of your hierarchy in the measure and then use it in whatever combination (or just parts of them). One has to hardcode the hierachy, because the order matters.

See enclosed file where the [Measure wrong order] delivers wrong results (workbook taken from this good article: https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/  )

[Anonymous]

Hi,

I am trying to port this measure into a tabular model. So a user can drag any attributes of any dimension into a matrix and get a weighted average which is calculated with the help of this Measure.

I ended up going with a measure that uses IsInscope multiple times for different scopes to find out what calculation is required.

Ie...

Switch(true,

IsInscope(MatterId),

Calculate(sum(Calculate(SUM([WIP]),ALLSELECTED(WIPHistory[MatterId)),

IsInscope(clientid),

Calculate(sum(Calculate(SUM([WIP]),ALLSELECTED(WIPHistory[clientid))

etc etc

I have written about 8-9 IsInScope for different possible dimension attributes like Matter Name, Office etc and resulting calculations however I will not be able to cover all dimensions in the model. The measure is running at a okayish speed due to 16 million rows. But it should be fine until i find a better Hierachy totalling solution.

Thanks for the recommendation IsInScope is great.