Search code examples
powerbidaxformulapowerbi-desktop

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?

Solution

  • 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.