cancel
Showing results for
Did you mean:
Helper II

## 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

Accepted Solutions
Super User III

## Re: How to create a Total Sum measure for a matrix?

Hi @Dl_BL_PB ,

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

Imke Feldmann

How to integrate M-code into your solutionCheck out more PBI- learning resources here -- Performance Tipps for M-queries

6 REPLIES 6
Solution Sage

## Re: How to create a Total Sum measure for a matrix?

Hello @Dl_BL_PB,

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

Helper II

## Re: How to create a Total Sum measure for a matrix?

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 III

## Re: How to create a Total Sum measure for a matrix?

Hi @Dl_BL_PB ,

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

Imke Feldmann

How to integrate M-code into your solutionCheck out more PBI- learning resources here -- Performance Tipps for M-queries

Helper II

## Re: How to create a Total Sum measure for a matrix?

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

Highlighted
Super User III

## Re: How to create a Total Sum measure for a matrix?

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

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

Imke Feldmann

How to integrate M-code into your solutionCheck out more PBI- learning resources here -- Performance Tipps for M-queries

Helper II

## Re: How to create a Total Sum measure for a matrix?

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.

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors