Hi all,
I need to create some dax measures to do like-for-like store sales performace comparison. Let me explain by example. We often need to compare a chain store performace, like walmart, against all stores (non-walmart stores) with the matching 5-digit zipcode. We name this "trade area" (TA) measures. We need to compare the sales quantity and store count between the selected chain and TA for any possible time frame (filter) in the report. To make it even worse, the TA definiton needs to only include chain stores zipcode which total store sales quantity >0 for selected time period. For store count, we need to apply the same TA definition for store total quantity > 0. The fact table store store quanity by product sku, week date, suppliers, etc..
Since the TA definition is dynamic based on chain selection and store total quantity for a selected period, so I can't create a static table to store TA data, instead, i have to create dax mesures for TA quanity and TA store count. I was able to do so and the performance is not that great since i have to apply the same rule for finding all qualified TA stores for each measure. I wonder if there is any way to define to TA data only once, then i can calculate the quantity and store count based on that TA data?
Thanks in advance.
Wenchi Wang
