Forum Discussion

jct999's avatar
jct999
Advocate II
6 years ago

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

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    Measure 2 = 

    var L=selectedvalue(table[Level])

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

    • jct999's avatar
      jct999
      Advocate II
      6 years ago

      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

       

      • AntrikshSharma's avatar
        AntrikshSharma
        Community Champion
        6 years ago

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