Percentage not showing correctly in matrix subtotals.

[chipchuck]

I am trying to create a Matrix 

The ROWS pull from a column with two values (PMO and FMO)

The COLS pull from a column with three values (Add, Modify, and Delete)

The VALUES are three measures (Total Count, Passed, and Failed)

I have a measure called %Passed 

% Passed = Detail[AUTOMATED]/CALCULATE([Order_cnt],ALLEXCEPT(Detail,Detail[FMO_PMO],Detail[PROGRAM NAME]))

And this calculates the % Passed inside where the row and column meets, but in the subtotals the calculation is greater than 100%.

Any advice on how to make it work in both values and subtotals?

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.

[MFelix]

Hi @chipchuck,

The measures are based on context so it take into account the slicers / filters or values in rows / columns to make the calculation so when you make a calculation and add it to the matrix it can give you different results since the context change.

When you use allexcept the filters are removed except the one on the column so when you look at the totals the context is different.

I'm assuming that your several values will add up to the total row correct so at max you will get 100%.

You need to do something like this:

% Passed Subtotals =
IF (
    HASONEVALUE ( Table[PMO/FMO] );
    [%Passed];
    SUMX ( ALL ( Table[Colum1, Table[Colum2] ); [% Passed] )
)

Two notes:

1. Column [PMO/FMO] should be the column that you use as row context on your table
2. In the SUMX you can use the full table instead of the ALL syntax however in terms of performance it's better to only use the data columns you are using on your table.

If you need additional help please share some data sample and expected output.

Regards,

MFelix

[chipchuck]

Says a circular dependency was detected.

% Auto = IF(HASONEVALUE(Detail[FMO_PMO]),[%Automated],SUMX(ALL(Detail[FMO_PMO],Detail[PROGRAM NAME],Detail[AUTOMATED],Detail[ORDER NUM]),[%Automated]))

[chipchuck]

I am trying to create a Matrix 

The ROWS pull from a column with two values (PMO and FMO)

The COLS pull from a column with three values (Add, Modify, and Delete)

The VALUES are three measures (Total Count, Passed, and Failed)

I have a measure called %Passed 

% Passed = Detail[AUTOMATED]/CALCULATE([Order_cnt],ALLEXCEPT(Detail,Detail[FMO_PMO],Detail[PROGRAM NAME]))

And this calculates the % Passed inside where the row and column meets, but in the subtotals the calculation is greater than 100%.

Any advice on how to make it work in both values and subtotals?