CODEWITHSUNDEEP
powerbidax
GaryR
GaryR

Reputation: 3

Display Sum of Target as Zero when one or Multiple cities in filter are selected from Static List

I have a table where Actual vs target is displayed,

I have targets for all States By Cities except for New York where I have overall State Target. So when Any of the cities in New York is selected I need target as Zero but when New York as state is selected I need actual Target.

I have created a custom column in Both TerritoryDim NewCity = IF(State = "New York", State ,City) and created a Many-Many relationship with Targets Table NewCity. Now the Sales table to TerritoryDim is on Actual City.

What I need is to show Targets as Zero if one or multiple Cities in NY are selected, But as a state it should be actual target. I can use a static list of cities for New York I tried SelectedValue(City) in. But this wont work. how can I solve this Than you

Upvotes: 0

Views: 17

Answers (1)

Michal
Michal

Reputation: 6089

Hard to say without seeing your actual tables and schema but you should be able to adapt the following for your needs:

Dynamic Target =
VAR SelectedCities = VALUES(TerritoryDim[City])
VAR SelectedState = SELECTEDVALUE(TerritoryDim[State])
VAR NYCities = {"New York City", "Albany", "Buffalo", "Rochester"}  -- Replace with actual cities in New York

-- Check if any cities from New York are selected
VAR IsNYCitySelected = 
    COUNTROWS(
        INTERSECT(
            SelectedCities, 
            NYCities
        )
    ) > 0

-- If cities in NY are selected, return 0. Otherwise, return the actual target.
RETURN
    IF(
        IsNYCitySelected && SelectedState <> "New York",
        0,
        CALCULATE(SUM(Targets[Target]))  -- Replace with your actual target column
    )

Upvotes: 0

Related Questions