cancel
Showing results for
Did you mean:  Helper III

## DAX :

Hi,

I have a table with 3 columns :

• COUNTRY (string)
• LEVEL (integer)
• Quantity (integer)

I want to set up a Pivot Table with :

• Line : COUNTRY
• Column : LEVEL
• Values : 3 measures :
• Measure1 = Sum(Qty) for selected COUNTRY and LEVEL
• Measure2 = Sum(Qty) for selected COUNTRY and next LEVEL (i.e. LEVEL + 1)
• Measure3 = Measure1 / Measure2

What is the DAX formula for Measure2 ?

Thanks

Regards

5 REPLIES 5  Super User

Measure 2 =

var L=selectedvalue(table[Level])

return calculate(sum(Table[quantity]),table[Level]=L+1)  Helper III

It works fine ! Thanks

Before asking on the forum, I tried SELECTEDVALUE but I put it in the CALCULATE expression, and it did not work.

So, The solution seems to use SELECTEDVALUE in a VAR expression...

Regards  Resident Rockstar

If you want to attain the same behaviour without variables then you will have to use explicit FILTER, for example.

``````Measure =
CALCULATE (
[Total Sales],
FILTER (
ALL ( Product[Brand] ),
Product[Brand] = SELECTEDVALUE ( Product[Brand] )
)
)``````

Because writing aggregation functions like SUM, AVERAGE, MAX are not allowed while doing boolean filter operations, and same is for SELECTEDVALUE, hence the following won't work.

``````Measure =
CALCULATE (
[Total Sales],
'Product'[Brand] = SELECTEDVALUE ( 'Product'[Brand] )
)``````  Super User

There's also the added complexity with FILTER (ALL()) - that may produce more results than intended in the visual context.  Resident Rockstar

Correct, in that case he can either wrap CALCULATE's filter arguments with KEEPFILTERS or use VALUES as per the requirements.   