Forum Discussion
Measure logic for different hierarchy level
Let's say I have a measure called [Qty]. It has a formula that differs from hierarchy level.
I could define [Qty] to measure the value correctly at product level.
But at the customer level, all I want is to sum up what has been calculated in the [Qty] at product level, how do I go about doing it?
There is a formula in DAX called HASONEVALUE(<columnName>). This will return true if the <columnName> mentioned in the formula returns one single value. I think you can use this in your measure as a boolean condition to determine which formula to use for calculating the result.
For example
Result = IF ( HASONEVALUE(Product), <product-level-formula>, <customer-level-formula> )
This means, at the product level, HASONEVALUE(Product) will return TRUE as a result based on the current product and at the customer level, HASONEVALUE(Product) will return FALSE because there will be multiple products.
For example, consider the following table.
CustomerProductQty
A X 10 A Y 20 A Z 30 B X 40 B Y 50 B Z 60 C X 70 C Y 80 C Z 100 Result = IF ( HASONEVALUE ( Tables[Product] ), SUMX ( Tables, Tables[Qty] / 2 ), SUMX ( Tables, Tables[Qty] ) )This measure will give the following result.
If you notice in the image above, the Result column is calculating differently at Customer Level and Product Level.
At Customer-level it is adding up the Qty field. But at the product level, it is dividing the Qty field.
See if this function can be used in your case.
4 Replies
There is a formula in DAX called HASONEVALUE(<columnName>). This will return true if the <columnName> mentioned in the formula returns one single value. I think you can use this in your measure as a boolean condition to determine which formula to use for calculating the result.
For example
Result = IF ( HASONEVALUE(Product), <product-level-formula>, <customer-level-formula> )
This means, at the product level, HASONEVALUE(Product) will return TRUE as a result based on the current product and at the customer level, HASONEVALUE(Product) will return FALSE because there will be multiple products.
For example, consider the following table.
CustomerProductQty
A X 10 A Y 20 A Z 30 B X 40 B Y 50 B Z 60 C X 70 C Y 80 C Z 100 Result = IF ( HASONEVALUE ( Tables[Product] ), SUMX ( Tables, Tables[Qty] / 2 ), SUMX ( Tables, Tables[Qty] ) )This measure will give the following result.
If you notice in the image above, the Result column is calculating differently at Customer Level and Product Level.
At Customer-level it is adding up the Qty field. But at the product level, it is dividing the Qty field.
See if this function can be used in your case.
I am aware of the DAX, in fact, I used "ISINSCOPE" to determine if I have at top hierarchy or bottom hierarchy. My challenge lies on how I am able to calculate differently within the same measure.
e.g. Top Hierarchy (Customer) - sum( value at product level)
Next Hierarchy (Product ) - product measure
Right now, I have to create two measures,
Measure 1 (Product) = <some calculation formula>
Measure 2 (Customer) - switch (TRUE, isinscope( Product), Measure_1,
sumx( calculatetable( summarize(Table, Product, "value", [Measure_1]), [value]) )I just wonder if there's a better way of doing, so I don't need to create 2 measures.
Dude you're a legend, thank you so much!
