Forum Discussion
Conditional Matrix Arrangement
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.
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/
4 Replies
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.
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
