PowerPivot Nuggets - Part 15 - What If Analysis with Slicers

Relationships are one of the cornerstones of PowerPivot.  In general every PowerPivot workbook should have relationships defined in the model.  However I found some very nice use cases where it makes sense not to define any relationships.

Let's take a look at this little example. Suppose we have a table with articles, cost prices and sales prices.

I want to see what happens with my margin (Sales Price - Cost Price) when I start giving discounts.   I create a second Discount table with possible discounts.  I can't create a relationship between the two tables because they don't have any columns in common.

I place the discounts on a slicers and now I  need to find the "selected discount".  Since there's no relationship between the tables the built-in filter concepts of PowerPivot don't apply here.

The DAX VALUES() function is my rescue.  VALUES() returns a one-column table that contains the distinct values from the specified column.  It doesn't make sense to calculate the discounted sales price when more than one discount is selected .  So I need to write a little wrapper around the VALUES() function to return BLANK() if more than one values is selected.

SelectedDiscount := 

           IF(HASONEVALUE(Discount[Discount])=1;
                      VALUES(Discount[Discount])
                     ;BLANK())


Now calculating the discounted value is very simple: SUM(Article[SalesPrice])*(1-VALUES(Discount[Discount]))

DiscountedPrice :=

           IF(HASONEVALUE(Discount[Discount]);
                      SUM(Article[SalesPrice])*(1-VALUES(Discount[Discount]))
                      ;BLANK())

And similar for the margin after discount in percentage:

MarginAfterDiscount:= 

                      IF(HASONEVALUE(Discount[Discount]);
                      (Article[DiscountedPrice]-SUM(Article[CostPrice]))/SUM(Article[CostPrice])
                      ;BLANK())

Now by simply clicking on the discount slicer I can see the effect of the discounts on my margin.