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





    2 comments:

    Anonymous said...

    Another great post !!!

    Anonymous said...

    Awesome !