CODEWITHSUNDEEP
powerbidaxformulapowerbi-desktop
luiz guilherme doelinger
luiz guilherme doelinger

Reputation: 3

DAX POWER BI -Sum of values based on last interview of each client

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

TABLE

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 formula:

formula

and it partially worked:

Result

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?

Upvotes: 0

Views: 101

Answers (1)

Marco Vos
Marco Vos

Reputation: 2967

I think this is what you need:

Media Pontos PPS aux = 
SUMX (
AuxFormularios,
VAR maximum =
    CALCULATE (
        MAX ( AuxFormularios[ID_ENTREVISTA] ),
        ALLEXCEPT ( AuxFormularios, AuxFormularios[MATRICULA_BENEFICIARIO] )
    )
RETURN
    IF ( AuxFormularios[ID_ENTREVISTA] = maximum, AuxFormularios[Pontos PPS], 0 )
)

This measure loops through all the rows in the table. For each row it checks if the AuxFormularios[ID_ENTREVISTA] is equal to the MAX (AuxFormularios[ID_ENTREVISTA]) for the MATRICULA_BENEFICIARIO in that row (VAR maximum). If it is, then the Pontos PPS will be added to the total. If it is not, then 0 will be added to the total.

Upvotes: 0

Related Questions