Hello - I am stumped and need some help. My objective is to add a column to either my dim or fact table that designates what quartile my dim (stores) belongs to based on the fact (sales amount). I can accomplish this using the Switch function, however the quartile assignments are static and do not change when I filter by region for example.
The dim table has StoreID and Region
The fact table has StoreID and Total Sales Amount
The desired outcome is to have a new column that indicates the quartile that store is assigned to (Q1, Q2, etc). And if I select a specific region, then I would like the store that remain after the filter is applied to have their quartile assignments reevaluated based on the new subset of stores and NOT the entire populatoin of stores.
Below is the calculated column formula I used to accomplish the static version of this quartile
Quartile Indicator =
SWITCH(TRUE(),
[Total Sales Amount] <= PERCENTILEX.INC(Sales', [Total Sales Amount], 0.25), "D",
[Total Sales Amount]<= PERCENTILEX.INC(Sales', [Total Sales Amount], 0.5), "C",
[Total Sales Amount] <= PERCENTILEX.INC(Sales', [Total Sales Amount], 0.75), "B",
[Total Sales Amount]> PERCENTILEX.INC(Sales', [Total Sales Amount], 0.75), "A"
)
Thank you in advance
