I am working on a table that I have successfully limited to a subset of my data using various thresholds on various measure. Now, I need to calculate a measure that includes an overall value for just the entries in the table. Here is what I am dealing with.
My base table is populated something like this:
| Product Category | Product Subcategory (Fltr) | YTD Sales | YTD GM % | Prev. YTD GM% | Eroded Gross Profit |
|---|
| Brass Widgets | Square | 1,000,000 | 10% | 11% | Need to calculate |
| Brass Widgets | Round | 500,000 | 12% | 14% | Need to calculate |
| Steel Widgets | Square | 2,500,000 | 7% | 8% | Need to calculate |
| Steel Widgets | Round | 800,000 | 9% | 10% | Need to calculate |
To identify the product categories to include in the table, I use this expression for Product Category to identify the relevant product categories:
AGGR(
if($(SalesYTD)>400000,
if(
($(CYTD_GM) - $(LYTD_GM))< 0, [Product SubCategory])),
[Product Category], [Product SubCategory])
To this point, everything works fine. However, where I am struggling is how to calculate the Eroded Gross Profit. My equation for that is:
Eroded Gross Profit = [YTD Sales]/(1-[Overall Prev. YTD GM %])
The Overall Prev. YTD GM % is calculated for all the entries in the table above but only the entries in the table. In this case - let's assume that previous years sales are the same for simplicity - that value is 9.6%.
I tried to put in a set analysis expression in the Eroded Gross Profit column that would calculate the total Previous Year Sales and Previous Year GP in dollars in order to calculate the previous year GM%. However, that returned the total for the entire data set, not just the rows that are included in the above table.
Next, I tried the following:
Sum(TOTAL
{< [Product SubCategory] = {'$(=
AGGR(
if($(SalesYTD)>400000,
if(
($(CYTD_GM) - $(LYTD_GM))< 0, [Product SubCategory])),
[Product Category], [Product SubCategory])
)'
>}
[LYTD GP]
)
/
Sum(TOTAL
{< [Product SubCategory] = {'$(=
AGGR(
if($(SalesYTD)>400000,
if(
($(CYTD_GM) - $(LYTD_GM))< 0, [Product SubCategory])),
[Product Category], [Product SubCategory])
)'
>}
[LYTD Sales]
)
Note: [LYTD GP] is not in the table but exists in my app.
I want the above to calculate the total Gross Profit for all lines included in the table, divide that by the corresponding Total Sales for the items in the table from last year, in order to arrive at the overall weighted Gross Margin for the previous year. My thinking was to use the same expression that I used to create a "filtered" Product SubCategory dimension in a set analysis to return a list of relevant subcategories. However, when I put this into my expression editor, I wind up with all 0s.
Is what I am trying to achieve possible? Do I have a syntax problem? Is there another way to compute this? For now, I am mocking up a dashboard for beta testing. Once we go through that, our BI team can do more formal development and create additional aggregations/coding/other-magic-I-don't-understand in the background scripts when the data is loaded from the data warehouse. However, I need something that works for beta testing, even if it is not optimal from a development perspective.
Any advice is appreciated!
