There are 2 primary ways to accomplish this:
- Data Blending
- Table Calculations
I've attached a workbook to demonstrate the options. Here's a little explanation.
- In the Data Blending example, I've duplicated the data connection (once of Regional Level and once for National Level). As you click through the connections, you'll notice that I enabled a blend on Month and Region for the regional level and enabled a blend on Month only for the national level. I renamed the [Sales] field in the copied connections to Regional Average and National Average to make it clear which was which. The blend allows me to aggregate at different levels of detail, defined by enabling/disabling the various linking fields.
- The table calculation uses a single data source and defines two calculated fields -- one for Regional Average and one for National Average. The two calculations actually use the same code:
WINDOW_AVG is a table calculation that gives you the average of an aggregation within a window of your data. The difference in the two calculations is how I defined the window. I applied the Regional calculation as Pane Down (right click the field on the Measure Values shelf, and select Compute Using --> Pane Down) as the pane is the region level. I applied the National calculation as Table Down (as the entire Table down defines the National Average)
The final thing to note with the table calculation is that if I want to filter values, I need to be careful not to use a regular quick filter as that would filter out values that contribute to the Regional/National averages. Instead, I used a table calculation as a filter (the code was LOOKUP(ATTR(Store), 0) to filter by store only after the WINDOW_AVG calculation had been done. That makes the 2nd option a little more complex, but it has the advantage of using only one data connection (and only one query to the underlying source -- instead of 3 as the first option -- though if you have thousands of stores, especially hundreds of thousands or millions, 3 small aggregate queries might be more efficient than one query that pulls back hundreds of thousands of records).
I hope that gives you some options. Feel free to ask any questions you might have!
StoreVsRegion.twbx 12.2 KB
Thanks you so much for the quick response! It solved my problem!
You're welcome! Just out of curiosity which option did you end up using?
Thanks Joshua. I used option 2. It’s more dynamic and easy to handle. Just need to understand the formula better. I used Window calculation before, never used lookup. Learned something new. Is there any document/samples that explain all the function/formulas in detail?
XM Data Strategies, Inc.
Without Data, All we have are opinions
You're welcome! I'm glad it worked for you.
Jonathan Drummey has a list of table calculation resources here that are really good: Want to Learn Table Calculations? Here’s How! | Drawing with Numbers