Calculate calculated fields by row

[pr1ngl3]

Afternoon - hoping someone can put me out of my misery....

Using the above as an example...

The following Tables have a relationship via a Customer Mapping table

Term is a calculated column from Contracts (basic datediff from Today() to term end date, in months)

GL has a calculated column to determine the last posting date of an invoice  

LastPostingDate = calculate(max(GL[Posting Date (bins)]),ALLEXCEPT(GL,GL[Customer Map]), GL[Posting Date] <= today())

Last Invoice is a measure to sum the value, filtering so the posting date is the same as the last posting date

LastInvoice = calculate(sum(GL[Amount]),filter(GL,GL[Posting Date (bins)] = GL[LastPostingDate]))

Remaining Revenue is a measure to multiply the 2 values

Remaining Revenue = calculate([LastInvoice] * MAX('Contracts'[Months Remaining]))

All this is working accurately per line (by customer) however, the Total (as expected) is also calulating in this was, summing the Last Invoice and multiplying by the MAX of the term remaining.  - highlighted in RED. Whereas, I need to calculate each line individually and sum that, highlighted in Green.

Any ideas?

Thanks in advance

[DimaMD · Accepted solution]

Hi @pr1ngl3 Try it maesure

IF( HASONEVALUE('table'[Customer]),
 [Remaining Revenue],
 SUMX( VALUES('table'[Customer]),
 [Remaining Revenue]))

[pr1ngl3]

Thank you so much!! been struggling with this for a while.

Such a quick reply too 😁