Measure logic for different hierarchy level

[meepoknang]

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? 

[Anonymous]

I want my measure to look like "Avg Sales Measure". at Unit level ,sales has to be SUM, Region and Zone level has to be AVG after the SUM. Please Help.Urgent.!

[Anonymous · Accepted solution]

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.

[Diego_Moreno]

Dude you're a legend, thank you so much!

[meepoknang]

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.