Conditional Matrix Arrangement

[Anonymous]

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.

[HashamNiaz]

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 · Accepted 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/

[Anonymous]

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

[dkaushik]

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.