3 Replies Latest reply on Mar 1, 2018 9:38 AM by Maxime PIERRE

# Creating a Dimension based on a Calculation?

I have store level data that has sales information by month for every store we have. I want to create a dimension that varies by average monthly store sales across all the data so I can compare that store to other stores of similar "size" by sales.

For example, I have a store that averages \$125K per month in sales. This is easy to see by just averaging sales across all month. I want this store to have a dimension("Average Monthly Sales") of '\$100K-\$150K along with all the other stores in the data set that also have an average monthly sales number between \$100-125K'. I want it to be calculated and not hard coded so that a store's "Average Monthly Sales" dimension changes if sales increase or decrease dramatically or steadily over time and therefore it needs to be reclassified. I also want it to be a dimension so that I can use Level of Detail calculations to compare it's other metrics to stores of similar size.

Each time I try to do this, I either can't get it to adjust when the data is in aggregate or it becomes a measure which I cannot use for level of detail calculations.

Help?

• ###### 1. Re: Creating a Dimension based on a Calculation?

Kaylan,

I'm not sure if this will work with your setup,

but I tried adding an extra layer of LOD.

In the attached, I created a calc called [fixedGroup]:

{ FIXED [State],[MonthOrder]:[SalesGroup]}

which in your setup would be something like :

{ FIXED [Store],[Month]:[SalesGroup]}

Where SalesGroup was something like:

IF AVG([Sales])<100 THEN "A"

ELSEIF AVG([Sales])<500 THEN "B"

ELSEIF AVG([Sales])<2000 THEN "C"

ELSE "D"

END

I didn't explore too carefully, but it appeared that

I was able to use [fixedGroup] in other LOD calculations like:

{ FIXED [fixedGroup]:SUM([Profit])}

2 of 2 people found this helpful
• ###### 2. Re: Creating a Dimension based on a Calculation?

I tried this, but it didn't work. At this point I had to do a workaround with another data source that calculates it and brings it in as a dimension. Not ideal as it has to be brought in every month and recalculated, but it worked.

Any other suggestions?

I did also discover that LOD calculations do not take filters into account. If you filter on year, you must also fix on year for the calculation to be correct.

Thank you for the help!

• ###### 3. Re: Creating a Dimension based on a Calculation?

Hello Kaylan,

For the LODs to be use with your filter, you have to add the filter into the context (right clic on the filter -> add to context).

LODs are executed just after contextual filters but before normal filters.

Sincerely.