Matrix Subtotals

[Anonymous]

Hi all,

I have a matrix as follows: 

I am trying to achieve both column and row totals of 100%. As you can see, this works correctly for the columns but for each row it sums the values for each person e.g. total of 1422.5 % should be 100%. How can I edit this measure or is there another workaround? I use the following measure as the values:

% Summary Total =

VAR SettlementVal = [Total]

VAR SelectedName = MAX(Times[name])

VAR AllSettlement =

CALCULATE (

    [Total],

    FILTER(ALLSELECTED(Times), Times[name] = SelectedName)

)

VAR Result = DIVIDE(SettlementVal, AllSettlement)

RETURN

    FORMAT(Result, "0.0%")

[Anonymous]

Hi @Anonymous 

Did your row field and the column field are from the same table? Can you provide some sample data so that can provide more suggestion for you.

Best Regards!

Yolo Zhu

[Anonymous]

Hi @Anonymous,

Thanks for your response. All data is from the same table:

[Anonymous]

Hi @Anonymous 

You can try the following measure

Measure =
SUMX ( VALUES ( Times[name] ), [% Summary Total] )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous 

Produced the error SUMX can't work with values of type String.

I have tried two measures that produce work for either columns totalling 100 or rows:

Hoping to combine them to have both the column and row overall totals = 100 cheers

[gmsamborn]

Hi @Anonymous 

I think the reason the SUMX() example didn't work is because [% of Total Hours] is returning a string because of FORMAT at the end of your measure.

Try just returning the variable Result.  You can then change the return type for the measure by selecting that measure and changing the format to percentage in the Measure Tools menu.

Let me know if this helps.

[Anonymous]

Hi @gmsamborn,

I adjusted the code as follows and combined with the code given by @Anonymous but no  success:

% Summary Total = VAR SettlementVal = [Total] VAR SelectedName = MAX(Times[name]) VAR AllSettlement = CALCULATE ( [Total], FILTER(ALLSELECTED(Times), Times[name] = SelectedName) ) VAR Result = DIVIDE(SettlementVal, AllSettlement) RETURN Result

[gmsamborn]

OK.  I just thought I'd check.

I'll let @Anonymous  continue with this one.