cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jct999
Helper III
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
lbendlin
Super User
Super User

Measure 2 = 

var L=selectedvalue(table[Level])

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

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

 

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] )
)

 

 

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

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.