Twitter Updates

    follow me on Twitter

    Thursday, February 09, 2012

    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 := 

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

    DiscountedPrice :=

    And similar for the margin after discount in percentage:


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


    Anonymous said...

    Another great post !!!

    Anonymous said...

    Awesome !