Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 % MeasureManager % Measure
North42%45%
John42%40%
Paul42%50%
East13%47%
Mike13%80%
Lois13%29%
Pete13%33%
West33%60%
Karen33%60%
South8%27%
Nick8%13%
Chris8%31%
Frank8%50%
Mel8%14%

 

And I want this as a result:

RESULTS MATRIX
 % Below Target
North42%
John40%
Paul50%
East13%
Mike80%
Lois29%
Pete33%
West33%
Karen60%
South8%
Nick13%
Chris31%
Frank50%
Mel14%

 

Thank you for your support.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

View solution in original post

4 REPLIES 4
HashamNiaz
Solution Sage
Solution Sage

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

amitchandak
Super User
Super User

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

Anonymous
Not applicable

Thank you, adapted the example to my data and works awesome, I really appreciate it.

dkaushik
Resolver II
Resolver II

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.