Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

PLEASE help with this equation

I have two types of data points for certain neighborhoods.

First is the type of customer, and second is the amount of customers.
Table 1
Location      Classification    Count
Location A    Residential     50
Location A    Commercial    0
Location B     Residential     100
Location B     Commerical    20
Location C     Residential      75
Location C      Commerical    5

I am trying to write a column that determines if 1) part of the sum contains "Commercial" and if so I want to multiple the total count for Location B by 2.7

If there is NO "Commercial", then I would multiply the total count of Location A by 3.

I already have a measure that summarizes the total counts for certain locations called [Total Count]

Population =
IF ( Table1[Classification] <> "commerical",
CALCULATE(SUMX(Table1, [Total Count] * 2.7)),
CALCULATE(SUMX(Table1, [Total Count] * 3)))
^ this is not working

Any help would be greatly appreciated, thanks!



Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    Try:

     

    Sum Count = SUM(Table1[Count])

     

     

     

    Population =
SUMX (
    ADDCOLUMNS (
        Table1,
        "_calc",
            CALCULATE (
                IF (
                    MAX ( Table1[Classification] ) = "Commercial" && MAX(Table1[ Location]) = "Location B",
                    [Sum Count] * 2.7,
                   IF( MAX ( Table1[Classification] ) <> "Commercial" && MAX(Table1[ Location]) = "Location A", [Sum Count] * 3
                )
            )
)
    ),
    [_calc]
)

     

     

     

     

     

     

     

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    Try:

     

    Sum Count = SUM(Table1[Count])

     

     

     

    Population =
SUMX (
    ADDCOLUMNS (
        Table1,
        "_calc",
            CALCULATE (
                IF (
                    MAX ( Table1[Classification] ) = "Commercial" && MAX(Table1[ Location]) = "Location B",
                    [Sum Count] * 2.7,
                   IF( MAX ( Table1[Classification] ) <> "Commercial" && MAX(Table1[ Location]) = "Location A", [Sum Count] * 3
                )
            )
)
    ),
    [_calc]
)

     

     

     

     

     

     

     

  • YukiK's avatar
    YukiK
    Impactful Individual
    4 years ago

    You'd need to add an additional condition that checkes if the location is A or B, etc, in addision to checking if "Commercial".

     

    Hope that helps!