Values not summing

[GunnerJ]

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.

[Ashish_Mathur]

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.

[Anonymous]

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.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490