Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))
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% |
Solved! Go to Solution.
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!
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
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.
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.
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!
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
Thank you! its a good lead, its a shame its not dynamic but it will have to do.Thanks!
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!
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |