Forum Discussion
Weird Sum Error
Hi All,
very strange one, let me explain using the below example data,
i have a table much like the below:
| Account | Revenue |
| Customer 1 | £100 |
Customer 2 | £50 |
| Customer 3 | £50 |
| Customer 4 | £1 |
| Customer 5 | £2 |
and i want to have a third column with a colour for whether the revenue is above or below the overall average (£40.60)
So i'm using the following formula for a measure (we'll call colour) i'm adding as a column to the table:
If(Sum(Table[Revenue])>Divide(Sum(Table[Revenue]),DistinctCount(Table[Account])),"Green","Red)
However that returns a table where all values above 0 are green:
| Account | Revenue | Colour |
| Customer 1 | £100 | Green |
| Customer 2 | £50 | Green |
| Customer 3 | £50 | Green |
| Customer 4 | £1 | Green |
| Customer 5 | £2 | Green |
However if i take the exact same code, but substitute the 2nd Sum function for the total number, it's then correct:
If(Sum(Table[Revenue])>Divide(203,DistinctCount(Table[Account])),"Green","Red)
so that only leads me to believe the 2nd Sum function is for some reason returning 0. this is the same case for any measures that work in other cards, or if i set up the SUM function as it's own measure.
the table i should get is this:
| Account | Revenue | Colour |
| Customer 1 | £100 | Green |
| Customer 2 | £50 | Green |
| Customer 3 | £50 | Green |
| Customer 4 | £1 | Red |
| Customer 5 | £2 | Red |
so confused why the same logical function would return two different values.... help please?
If I understood the purpose of your measure correctly you would have to remove row context to calculate your average Revenue to be able to use it in a table:
e.g.
Colour =VAR TotalRevenue = CALCULATE(SUM('Table'[Revenue]), ALL('Table'))VAR TotalCustomers = CALCULATE(DISTINCTCOUNT('Table'[Account]), ALL('Table'))VAR AvgRevenuePerCustomer = DIVIDE(TotalRevenue, TotalCustomers)VAR CurrentRevenue = SUM('Table'[Revenue])RETURNIF(CurrentRevenue > AvgRevenuePerCustomer,"Green","Red")
5 Replies
worked it out, if i wrapped the ALL() Table in a Filter() and reapplied then it worked.
Hi, would you mind to provide the corresponding .pbix file?
Hi IAmAPowerBIUser ,
unfortunately not, as it contains sensitive information. I can answer any questions you might have however.
thanks
If I understood the purpose of your measure correctly you would have to remove row context to calculate your average Revenue to be able to use it in a table:
e.g.
Colour =VAR TotalRevenue = CALCULATE(SUM('Table'[Revenue]), ALL('Table'))VAR TotalCustomers = CALCULATE(DISTINCTCOUNT('Table'[Account]), ALL('Table'))VAR AvgRevenuePerCustomer = DIVIDE(TotalRevenue, TotalCustomers)VAR CurrentRevenue = SUM('Table'[Revenue])RETURNIF(CurrentRevenue > AvgRevenuePerCustomer,"Green","Red")Hi IAmAPowerBIUser ,
I notice that the All() function ignores my current page filters, which is a problem - how do i execute the above code whilst retaining my filters?
