Show facts in different measurements using DAX and PowerPivot

Yesterday got a question where a customer pulled a raw fact table into a Tabular model, the customer would like to take a look at all the orders by the customer in different measurements. The multiple measurements are stored in the Material Master table, so if the customer wishes to report items in Cases, Pallets, Cubic meters, etc they can do so.

This straight forward in DAX but I still decided to do a blog post instead of answering in an email as this might help others.

Lets say I have the following two tables:

One is the fact table with products of a certain color in stock based on a certain measurement. I also have a table that describes each measurement and the ratio to calculate the measurement to Cubic Metrics. After loading these into Power Pivot I DO NOT create a relationship between the two tables.

The first thing that I want to do is create a unified stock value that is all based on a single measurement. I choose cubic metrics. I add a new calculated column to the Stock table that calculates the Stock in Cubic metrics by getting the ratio from the measurements table using LookupValue and divide that by the stock number:

StockInCubicMetrics :=

DIVIDE ( [Stock],

LOOKUPVALUE ( Measurement[Ratio],

Measurement[MeasurementId], Stock[MeasurementId]

)

)

This gives me the Stock in the single measurement:

Next I create a simple measure that I can use in the PivotTables or other measures:

Sum of StockInCubicMetrics:=SUM([StockInCubicMetrics])

Now finally I want to create a measure that will allow the user to select the measurement he wants to display his stock value in, we will be using a slicer or a filter to select the measurement in the report. First I need to be able to get the ratio of the selected measurement:

RatioValue:=IF(HASONEVALUE(Measurement[Measurement]),VALUES(Measurement[Ratio]),1)

Here I used HASONEVALUE to check if one one Measurement is selected. If so return the ration for that measurement if not return 1.

Next I need to create the measure that on the fly calcualtes the stock based on a selection of the measurement:

StockCalculation:=DIVIDE([Sum of StockInCubicMetrics], [RatioValue])

Here is divide the sum of stock in cubic metrics by the ration determined by the selection of the measurement. Again I want to point out that no relationship is used in this model. I use the DAX expression ratiovalue to get the ratio for the measurement I select. I don’t need nor want the behavior that creating a relationship would give me.

Putting it all together:

In this case the Stock calculation measure shows the stock in “cases” as that is selected by the slicer.

You can download the sample workbook here: http://sdrv.ms/JzykOt