Sum the value of one column based on the maximum value of another column

2
votes

I need a measure to add the last non blank value from column SaldoConta from the table pcsa below from each column conta.

The criteria to define last non blank is the one with maximum value in column AnoMes.

conta   ano mes AnoMes  SaldoConta
110 2017    2   201702   112,56   
110 2017    0   201700   112,56   
111 2017    4   201704   70,47   
111 2017    0   201700   78,06   
111 2017    1   201701   70,47   
111 2017    2   201702   70,47   
112 2017    8   201708   100,00   
112 2017    3   201703   242,16   
112 2017    10  201710   100,00   
112 2017    2   201702   200,00   
112 2017    11  201711   100,00   
112 2017    12  201712   100,00   
112 2017    5   201705   240,75   
112 2017    1   201701   200,00   
112 2017    4   201704   242,16   
112 2017    0   201700   100,00   
112 2017    9   201709   200,00   
112 2017    6   201706   683,67   
112 2017    7   201707   200,00   
113 2017    2   201702   72 865,90   
113 2017    0   201700   21 114,37   
113 2017    3   201703   55 572,24   
113 2017    1   201701   39 967,48   
114 2017    11  201711   0,00   
114 2017    0   201700   92,59   
114 2017    8   201708   46,40   
114 2017    7   201707   84,50   
115 2017    0   201700   12,00   
116 2017    0   201700   8,27

I've tried a million different DAX expressions but it seems impossible to get what I want.

It should add only the LAST nonblank value in AnoMes, so for conta 110, it should be 112,56.

So, the table above should be:

conta   ano mes AnoMes  SaldoConta
110 2017    2   201702   112,56   
111 2017    4   201704   70,47   
112 2017    12  201712   100,00   
113 2017    3   201703   55 572,24   
114 2017    11  201711   0,00   
115 2017    0   201700   12,00   
116 2017    0   201700   8,27
1
powerbidax
Can you give at least a couple rows of the output you expect? – Alexis Olson
Please see my edit above, thanks! – user882670
By last, you mean the maximum AnoMes for each conta? – Degan
@Degan yes, exactly – user882670

1 Answers

1
votes

If you have conta as the rows in a matrix or table visual, then you can define Last SaldoConta as follows:

= SUMX(FILTER(pcsa, pcsa[AnoMes] = MAX(pcsa[AnoMes])), pcsa[SaldoConta])

Matrix Visual

If you don't have the filter context for conta or you need it to subtotal correctly, then you need to do a bit more work, but the idea is the same.

Last SaldoConta = 
    VAR Summary = SUMMARIZE(pcsa,
                      pcsa[conta],
                      "LastSaldoConta",
                      SUMX(
                          FILTER(pcsa, pcsa[AnoMes] = MAX(pcsa[AnoMes])),
                          pcsa[SaldoConta]))
    RETURN SUMX(Summary, [LastSaldoConta])

Inside the SUMMARIZE we're creating the filter context for each distinct conta value and calculate the last SaldoConta. Then we sum all of those up.