Adjust the sum of totals and subtotals in a matrix

[rsouza023]

I have 3 levels of dimension for a matrix.

LOS > BU > MANAGEMENT LEVEL

The calculation by Management Level is correct.

The aggregation by BU is correct.

But I can't make the LOS aggregate the values ​​of each BU.

My dax measure:

A = 

VAR SUBTOTAL = CALCULATE([HORAS BUDGET], ALLSELECTED('DIM-CALENDARIO'), ALLSELECTED('DIM-CATEGORIA'))

VAR CALC = CALCULATE(-(([% HORAS ACTUAL] * SUBTOTAL) * [SCALE CEM] ))

VAR TOTAL = SUMX(VALUES('DIM-CATEGORIA'[Managment Level]), CALCULATE(-(([% HORAS ACTUAL] * SUBTOTAL) * [SCALE CEM] )))


RETURN
IF(HASONEVALUE('DIM-CATEGORIA'[Managment Level]), CALC, TOTAL)

[Anonymous · Accepted solution]

Hi @rsouza023 ,

Try creating the first measure like

A =
VAR SUBTOTAL =
    CALCULATE (
        [HORAS BUDGET],
        ALLSELECTED ( 'DIM-CALENDARIO' ),
        ALLSELECTED ( 'DIM-CATEGORIA' )
    )
VAR TOTAL =
    SUMX (
        VALUES ( 'DIM-CATEGORIA'[Managment Level] ),
        CALCULATE ( - ( ( [% HORAS ACTUAL] * SUBTOTAL ) * [SCALE CEM] ) )
    )
RETURN
    TOTAL

Then create another measure like

B =
SUMX ( 'DIM-CATEGORIA', [A] )

We often have problems with subtotal errors in the matrix, which can basically be solved by creating a new measure with the SUMX function. You can also download my attachment to see this example.

If there are still problems, I hope you could provide me with some dummy data.

Best Regards,

Stephen Tao

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

[Whitewater100]

Hello:

You can try 

      SUMX(VALUES(Table{column name that isn't working]), [A]))

[rsouza023]

I tried but it didn't change the values ​​I sent in the images above.

In theory, I need to make him add the values ​​by BU, without iterating the entire base again.

Maybe if I create a virtual table per BU, but I don't know how to do that.

[Whitewater100]

OK. Sorry about that. I have one other approach you could try. Also, if you feel like making some sample(fake data) to send me I'm pretty sure I can get it going for you. Always easier if you see the model and the visual you are trying to make.

That said you can try: Bold it to notice it. Italics for subbing in your BU table[column]

A = Measure that works for everything esle. Keep this.

New Measure:

VAR __table = SUMMARIZE('Table1',[Category1],"__value",[A])

RETURN

IF(HASONEVALUE(Table1[Category1]),[A],SUMX(__table,[__value]))