Forum Discussion

pr1ngl3's avatar
pr1ngl3
Regular Visitor
3 years ago
Solved

Calculate calculated fields by row

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

  • Hi pr1ngl3 Try it maesure

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

2 Replies

  • DimaMD's avatar
    DimaMD
    Solution Sage

    Hi pr1ngl3 Try it maesure

    IF( HASONEVALUE('table'[Customer]),
     [Remaining Revenue],
     SUMX( VALUES('table'[Customer]),
     [Remaining Revenue]))
    • pr1ngl3's avatar
      pr1ngl3
      Regular Visitor

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

      Such a quick reply too 😁