topic Re: Total value of table wrong based on summarized rows in DAX Commands and Tips

Total value of table wrong based on summarized rows

GuilhermeDoeli — Sat, 17 Jun 2023 14:57:33 GMT

Hi everybody! May someone help me with dax formula for this problem please? I tried so much but i cant resolve that...

I have this table with three columns (MATRICULA, PONTOS, ID). My objective is to create a dax formula that sum the total amount of PONTOS of the last interview of a client (highest ID_ENTREVISTA) for each client (MATRICULA_BENEFICIARIO).

So the formula should return: 40 + 25 + 0 +80 + 90 + 100 = 335

I tried this dax fórmula:

and it partially worked:

The formula brings the last interview of each client, but doesnt sum correctly. Instead of "90" the total should be "335".
I tried alot of different formulas but i cant get the correct sum.

May someone help me please?

Re: Total value of table wrong based on summarized rows

barritown — Tue, 20 Jun 2023 08:34:42 GMT

Hi GuilhermeDoeli,

If it is okay to replace BLANK with a zero, you can use the approach below:

In plain text:

MEDIA Pontos totals = VAR _tbl = SUMMARIZE ( data, data[BENEFICIARIO], "Totals", VAR CurrentBENE = [BENEFICIARIO] VAR MaxID = MAXX ( FILTER ( data, data[BENEFICIARIO] = CurrentBENE ), [ID] ) RETURN MINX ( FILTER ( data, data[BENEFICIARIO] = CurrentBENE && data[ID] = MaxID ), [Pontos] ) ) VAR Res = SUMX ( _tbl, [Totals] ) RETURN COALESCE ( Res, 0 )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Re: Total value of table wrong based on summarized rows

devanshi — Tue, 20 Jun 2023 09:29:17 GMT

Total = SUMMARIZE(Tablename, " ",
CALCULATE(
SUM([ PONTOS PPS]), ALLEXCEPTED(Tablename,[MATRICULA], [ID_ENTREVISTAID_ENTREVISTA] = MAX([ID_ENTREVISTA]))
)
)