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:
Another great post !!!
Awesome !
Post a Comment