Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I need to create a matrix so that it recognizes and reflects the division reults on the division row and the manager results in the manager rows. Measures are different (separated) calculated measures (for division and manager) since they have their own considerations.
I have this (notice how I'm getting averages for the division rows in column 3, this is wrong since division have their own results):
Division % Measure | Manager % Measure | |
North | 42% | 45% |
John | 42% | 40% |
Paul | 42% | 50% |
East | 13% | 47% |
Mike | 13% | 80% |
Lois | 13% | 29% |
Pete | 13% | 33% |
West | 33% | 60% |
Karen | 33% | 60% |
South | 8% | 27% |
Nick | 8% | 13% |
Chris | 8% | 31% |
Frank | 8% | 50% |
Mel | 8% | 14% |
And I want this as a result:
RESULTS MATRIX | |
% Below Target | |
North | 42% |
John | 40% |
Paul | 50% |
East | 13% |
Mike | 80% |
Lois | 29% |
Pete | 33% |
West | 33% |
Karen | 60% |
South | 8% |
Nick | 13% |
Chris | 31% |
Frank | 50% |
Mel | 14% |
Thank you for your support.
Solved! Go to Solution.
@Anonymous , I think isinscope can help
if(isincope(Table[Manager]), [Manager % Measure] , []Division % Measure])
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
Hi !
You can use below DAX to get your desired output;
% Below Target = IF( ISINSCOPE(YourTable[Division]), [Division % Measure], [Manager% Measure])
You can use ISINSCOPE() DAX function to filter the context.
Regards,
Hasham
@Anonymous , I think isinscope can help
if(isincope(Table[Manager]), [Manager % Measure] , []Division % Measure])
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
Thank you, adapted the example to my data and works awesome, I really appreciate it.
Hi,
For this you %age measure should calculate based on numerator and denominator. for eg:
%age = divide(sum(numerator), sum(denominator))
Thanks,
Dheeraj
If this post helps, then please consider Accept it as the solution and give thumbs up to help the other members find it more quickly.
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |