Weird Sum Error

[Redacted_VAR]

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?

[Redacted_VAR]

worked it out, if i wrapped the ALL() Table in a Filter() and reapplied then it worked. 

[Redacted_VAR]

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?

[IAmAPowerBIUser · Accepted solution]

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])

RETURN

    IF(

        CurrentRevenue > AvgRevenuePerCustomer,

        "Green",

        "Red"

    )

[Redacted_VAR]

Hi @IAmAPowerBIUser ,

unfortunately not, as it contains sensitive information. I can answer any questions you might have however.

thanks

[IAmAPowerBIUser]

Hi, would you mind to provide the corresponding  .pbix  file?