cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dl_BL_PB Helper II
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/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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

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

Hi @Dl_BL_PB ,

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

 

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 solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

 

View solution in original post

6 REPLIES 6
rajulshah Solution Sage
Solution Sage

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

Hello @Dl_BL_PB,

Please refer following link. Maybe it could help.
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=619c7481-...

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

Dl_BL_PB Helper II
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-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.

 

Super User III
Super User III

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

Hi @Dl_BL_PB ,

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

 

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 solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

 

View solution in original post

Dl_BL_PB Helper II
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
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 solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

 
Dl_BL_PB Helper II
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.

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

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