Setting dimension elements to a secondary axis
Hi,
I don't know if the following is even possible using an OLAP cube model. I'm looking to create a graph to view the YoY data with the percentage change overlaid on the secondary axis as a line graph. The two YoY data points in dark green and yellow on the graph have each been created by aggregating a list of elements (see below). Which have been selected as dimension elements.
I thought I could achieve this by calculating the percentage difference between the current vs previous member, and setting it as a measure, however when I add the measure in it applies to all the elements selected in the dimension. In the other instance, if I set this calculation as a dimension I can't then apply it to a secondary axis.
I also cannot create the YoY formulas as measures because aggregate functions cannot be used on calculated member's in the measures dimension.
To summarise, i basically want to keep the elements on the graph highlighted by the arrows.
Thanks in advance
1 reply
-
Hi ,
If I have understood you correctly, you have a calculated Measure that you wan to compare YoY for a selected list of weeks from each year, then calculate the % variance between the two for a set of categories on the x axis.
Start by creating an Aggregate Member in the Weeks dimension for current year. In this example I have selected weeks 1 through 26 explicitly. Of course this list could be dynamic, driven by parameters to allow the user to select the list of weeks they require.
Then the same for the previous year:
Then create the first Measure using the calculated measure (Sales - Cost in my example) and the Aggregate Member for the current year in the Data Point:
Second Measure using the Aggregate Member for the previous year in the Data Point:
Then the % Variance Measure:
Then put these Measures on a chart with the % Variance on the secondary axis:
I've added a reference line for the % Variance at zero.
Hope that helps.
Ian