Forum Discussion

Redacted_VAR's avatar
Redacted_VAR
Helper I
2 years ago
Solved

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 Cus...
Need Help
  • IAmAPowerBIUser's avatar
    IAmAPowerBIUser
    2 years ago

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

     

     

     

IAmAPowerBIUser's avatar
IAmAPowerBIUser
Frequent Visitor
2 years ago

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