topic Re: Total Sum in matrix in DAX Commands and Tips

Total Sum in matrix

Anonymous — Thu, 09 Feb 2023 14:45:21 GMT

Hi all,

I have a matrix where I calculate a value for each cell, and I need the total to be the sum of the values shown on the matrix.

This is the measure that is being used in the matrix:

Oportunidade = [Delta KPI]*calculate(SUMX('Reajuste coletivo','Reajuste coletivo'[VL_OBRIGACAO_MES_CONTRATO]),'Reajuste coletivo'[DT_REFERENCIA]="2023")*MEDIAN('Reajuste coletivo'[REAJUSTE])*12/100

where Delta KPI =

CALCULATE('Reajuste coletivo'[% isento],'Reajuste coletivo'[DT_REFERENCIA]="2022")-CALCULATE('Reajuste coletivo'[% isento],'Reajuste coletivo'[DT_REFERENCIA]="2023")

Re: Total Sum in matrix

tamerj1 — Thu, 09 Feb 2023 15:46:59 GMT

Hi Anonymous

please try

Oportunidade =
SUMX (
SUMMARIZE (
'Reajuste coletivo',
'Reajuste coletivo'[column in rows of the matrix],
'Reajuste coletivo'[column in columns of the matrix]
),
[Delta KPI]
* CALCULATE (
SUMX ( 'Reajuste coletivo', 'Reajuste coletivo'[VL_OBRIGACAO_MES_CONTRATO] ),
'Reajuste coletivo'[DT_REFERENCIA] = "2023"
)
* MEDIAN ( 'Reajuste coletivo'[REAJUSTE] ) * 12 / 100
)

Re: Total Sum in matrix

Anonymous — Thu, 09 Feb 2023 16:36:25 GMT

Hi,

The column in the rows of the matrix is in another table, so the summarize part is not working.

Re: Total Sum in matrix

tamerj1 — Thu, 09 Feb 2023 16:46:30 GMT

Anonymous

In this case you can use

CROSSJOIN ( VALUES ( Table1[Column] ), VALUES ( Table2[Column] ) )

Re: Total Sum in matrix

Anonymous — Thu, 09 Feb 2023 16:55:32 GMT

for some reason its still not summing up to the correct values:

Oportunidade =

SUMX (

CROSSJOIN ( VALUES ( 'De para filial'[ESTADO] ), VALUES ( 'Reajuste coletivo'[Faixa de vidas]) ),

[Delta KPI]

* CALCULATE (

SUMX ( 'Reajuste coletivo', 'Reajuste coletivo'[VL_OBRIGACAO_MES_CONTRATO] ),

'Reajuste coletivo'[DT_REFERENCIA] = "2023"

)

* MEDIAN ( 'Reajuste coletivo'[REAJUSTE] ) * 12 / 100

)

Re: Total Sum in matrix

tamerj1 — Thu, 09 Feb 2023 17:00:09 GMT

Anonymous

Sorry I missed to wrap MEDIAN with CALCULATE

Oportunidade =

SUMX (

CROSSJOIN ( VALUES ( 'De para filial'[ESTADO] ), VALUES( 'Reajuste coletivo'[Faixa de vidas]) ),

[Delta KPI]

* CALCULATE (

SUMX ( 'Reajuste coletivo', 'Reajuste coletivo'[VL_OBRIGACAO_MES_CONTRATO] ),

'Reajuste coletivo'[DT_REFERENCIA] = "2023"

)

* CALCULATE ( MEDIAN ( 'Reajuste coletivo'[REAJUSTE] ) ) * 12 / 100

)

Re: Total Sum in matrix

Anonymous — Thu, 09 Feb 2023 17:00:37 GMT

for some reason its still not summing up to the correct values:

Oportunidade =

SUMX (

CROSSJOIN ( VALUES ( 'De para filial'[ESTADO] ), VALUES ( 'Reajuste coletivo'[Faixa de vidas]) ),

[Delta KPI]

* CALCULATE (

SUMX ( 'Reajuste coletivo', 'Reajuste coletivo'[VL_OBRIGACAO_MES_CONTRATO] ),

'Reajuste coletivo'[DT_REFERENCIA] = "2023"

)

* MEDIAN ( 'Reajuste coletivo'[REAJUSTE] ) * 12 / 100

)

Re: Total Sum in matrix

Anonymous — Thu, 09 Feb 2023 17:02:05 GMT

Perfect! Thank you so much