Forum Discussion

GunnerJ's avatar
GunnerJ
Post Patron
6 years ago

Values not summing

I need to show the sum of a customer's bills (they can have multiple account #s but when I try to sum them up or use the filters it's not working as expected. 

 

This was my attempt to calculate the sum but it doesn't like the true/false expression which comes from the "MOST RECENT BILL = 1". The 1 marker is what determines if it's actually the most recent bill for that account number so I need it. 

 

Customer last bill total = CALCULATE(SUM('Customer Bills'[Sum of Charges by Month]),ALLEXCEPT('Customer Bills','Customer Bills'[Sum of Charges by Month]), [Most Recent Bill] = 1)
 
Is there away around this error that I'm just missing? When I show the values in a table it'll sum them at the end but if I put it in a bar chart it completely ignores the 1 value and just gives me a total of all the values.
Need Help
Tutorial Requests

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi GunnerJ ,

    Try using the FILTER function when filtering with a true/false expression along with CALCULATE, just like the below:

     

    Customer last bill total = CALCULATE(SUM('Customer Bills'[Sum of Charges by Month]),
ALLEXCEPT('Customer Bills','Customer Bills'[Sum of Charges by Month]), 
FILTER('Customer Bills',  [Most Recent Bill] = 1))

    This should fix the error.

     

    Cheers,
    Robin

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    Is [Most recent bill] a column or a measure?  If at all, you should be writing that as a calculated column.  My hunch is that this problem can be solved even without creating the [Most recent bill] calculated column formula.  I can offer more help, if you share the link from where i can download your PBI file and also show the expected result there.