Dynamic Charting In Power Pivot

Guest Post by Idan Cohen From Excelando

Rob already blogged about charts with dynamic measures –Using Named Sets for “Asymmetric” pivots, where you can choose the measures to be displayed with a slicer.

But what about dynamic axis?

This clever technique was found by one of my analysts , Gal Vekselman, when a client challenged us.
What is it useful for you ask ??!

For example, I want a chart to display sales by quarter and then change it to sales by month with a press of a button?
Or another useful scenario where I want to see sales by category,  but when choosing a category on the slicer,  the sub categories for this category will be displayed in the chart,  and when choosing a sub category the chart will display the underlying products.  Sounds cool, huh? Well,  it is even cooler.

And the way to do it? Named sets!

Use the period selection slicer to quickly change the chart axis, from Month to Quarter

A Practical example

Download Example Workbook

You are working for a bicycle firm. The sales manager asks you to create two Sales views:

A. Dynamic report of sales amount, Order quantity and average price. Where the charts will display the data monthly or quarterly by selection.

B. A drill down analysis by Category, Subcategory and product’s name. this analysis will be displayed in a chart according the following methodology:

a. Three slicer of Category, Subcategory and product name will be presented.

b. When all category values selected the chart will present the first hierarchy.

c. When only one category value is selected the chart will present all the subcategory values.

d. When only one subcategory value is selected is selected the chart will present all products’ name.

e. The chart has to present the data in month or quarter according to selection.

In the old days (before this MDX cool trick) we used to:

i. Create multiple PivotTables.

ii. Create Dynamic Ranges with OFFSET function.

iii. Debug errors.

iv. Sometimes use the CHOOSE function.

v. And more, and more and more.

You get the picture.

Now we simply need to create two simple measures and use them in two simple MDX sentences.

The how to

First, create a disconnected slicer for the period selection.

Second, build two calculated fields (Measures) using DAX:

A. Measure that returns the selected period.

[PeriodSelection] =

      IF(
        HASONEFILTER(tbl_PeriodSelection[Period Selection])
,VALUES(tbl_PeriodSelection[Period Selection])
,”Month”
)

(P3 Adaptive Note: Learn about the HASONEFILTER function and more)

B. Measure that returns the wanted hierarchy to present in the chart.

[ProductHierarchy] =

  IF(
HASONEFILTER(DimProductCategory[EnglishProductCategoryName]),
IF(
HASONEFILTER(DimProductSubCategory[EnglishProductSubcategoryName])
,3,2
)
,1)

Third, you have to create a relevant hierarchy – in this scenario you need to create two hierarchies:

A. Period hierarchy – Quarter -> Month (this will be created from the DimDate table)

B. Product Hierarchy – EnglishProductCategoryName -> EnglishProductSubcategoryName -> EnglishProductName (this will be created from the ProductMaster table)

Fourth, go to the Manage Sets editor

In the editor click on the “New” button and select the “Create set using MDX” option

Now, the MDX editor will open:

A. To create the “PeriodSet” insert the IIF function from the “All” folder

This will present the following syntax in the MDX editor

Replace the «Logical Expression» with the expression:

[Measures].[PeriodSelection]=”Month”

In the TRUE value (the first «object») you need to select the Month members’ folder of the Quarter Month hierarchy level and press the insert button

In the FALSE value (the second «object») you need to select the Quarter members’ folder of the Quarter Month hierarchy level and press the insert button

IIF([Measures].[PeriodSelection]=”Month”, [DimDate].[Quarter Month].[Year Month], [DimDate].[Quarter Month].[Year Quarter])

Before submitting pay attention that the check box are selected as shown

B. To create the “Products Sets” insert the IIF function from the “All” folder and
Repeat the above process but this time the MDX syntax is:

IIF([Measures].[ProductHierarchy]=3, [ProductMaster].[Product Hierarchy].[EnglishProductName], IIF([Measures].[ProductHierarchy]=2, [ProductMaster].[Product Hierarchy].[EnglishProductSubcategoryName], [ProductMaster].[Product Hierarchy].[EnglishProductCategoryName]))

Now the sets will appear in the DimDate and ProductMaster tables.

For the first chart put the period set in the Row field and the Sales in the Values. (Don’t forget to connect a period selection slicer)

For the second chart put the period set in the Row field, the Products Sets in the column field and the Sales in the Values field. (Don’t forget to connect a period selection slicer)

The result !

The Period Chart:

Use the period selection slicer to quickly change the chart axis, from Month to Quarter

The Product Hierarchy Chart:

No filters, Displaying Product Category Level

Multiple Categories chosen, Still displaying Product Category Level

Single Category Selected – Displaying All Subcategories For this Category

A Single Sub Category  Is selected on slicer – All products on this Sub category are displayed

Oh! and note that the period axis is also dynamic using the first trick !

Download Example Workbook