cancel
Showing results for
Search instead for
Did you mean:
Anonymous
Not applicable

## 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/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
1 ACCEPTED SOLUTION
Super User

Hi @Anonymous ,

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

6 REPLIES 6
Solution Sage
Anonymous
Not applicable

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.

Super User

Hi @Anonymous ,

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Anonymous
Not applicable

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

Super User

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/  )

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Anonymous
Not applicable

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.

## Helpful resources

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

#### Check it Out!

Click here to read more about the December 2021 Updates!

#### Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

#### Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors