Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Issue continue

Hi Guys,

I'm looking for DAX query to substract Actual-Budget to reflect on the % cell. Please see screen shot below

 

 



  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    This is a continuation of this post 

    https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dax-CALCULATION-ISSUE/m-p/2485927#M68256

    View solution in original post
    You can Create measures as follows

     

    Bud & Act Var = [Actual Amount] - [Budget Amount]
    Bud & Act Var % = 
VAR CurrentLineNum = SELECTEDVALUE ( Finance[Line Number] )
VAR NetGPLineNum = CALCULATE ( VALUES ( 'Gross margin'[Net GP] ), 'Gross margin'[GM] = CurrentLineNum )
VAR SalesLineNum = CALCULATE ( VALUES ( 'Gross margin'[Sales] ), 'Gross margin'[GM] = CurrentLineNum )
VAR NetGP =
    CALCULATE (
        [Bud & Act Var],
        ALLEXCEPT ( Finance, Finance[Fiscal Year] ),
        Finance[Line Number] = NetGPLineNum
    )
VAR SalesGoods =
    CALCULATE (
        [Bud & Act Var],
        ALLEXCEPT ( Finance, Finance[Fiscal Year] ),
        Finance[Line Number] = SalesLineNum
    )
VAR Result =
    FORMAT ( DIVIDE ( NetGP, SalesGoods ), "Percent" )
RETURN
    Result
    Bud & Act Var Values = 
VAR CurrentLineNum = SELECTEDVALUE ( Finance[Line Number] )
RETURN
    IF ( 
        CurrentLineNum IN VALUES ( 'Gross margin'[GM] ),
        [Bud & Act Var %],
        [Bud & Act Var]
    )

     

  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 

    Yes

    Bud & Act Var Values = 
VAR CurrentLineNum = SELECTEDVALUE ( Finance[Line Number] )
RETURN
    IF ( 
        CurrentLineNum IN VALUES ( 'Gross margin'[GM] ),
        BLANK ( ),
        [Bud & Act Var]
    )

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , This seems like measure to dimension conversion, you need a calculation group

     

    Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0

     

     

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 
    This is a continuation of this post 

    https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dax-CALCULATION-ISSUE/m-p/2485927#M68256

    View solution in original post
    You can Create measures as follows

     

    Bud & Act Var = [Actual Amount] - [Budget Amount]
    Bud & Act Var % = 
VAR CurrentLineNum = SELECTEDVALUE ( Finance[Line Number] )
VAR NetGPLineNum = CALCULATE ( VALUES ( 'Gross margin'[Net GP] ), 'Gross margin'[GM] = CurrentLineNum )
VAR SalesLineNum = CALCULATE ( VALUES ( 'Gross margin'[Sales] ), 'Gross margin'[GM] = CurrentLineNum )
VAR NetGP =
    CALCULATE (
        [Bud & Act Var],
        ALLEXCEPT ( Finance, Finance[Fiscal Year] ),
        Finance[Line Number] = NetGPLineNum
    )
VAR SalesGoods =
    CALCULATE (
        [Bud & Act Var],
        ALLEXCEPT ( Finance, Finance[Fiscal Year] ),
        Finance[Line Number] = SalesLineNum
    )
VAR Result =
    FORMAT ( DIVIDE ( NetGP, SalesGoods ), "Percent" )
RETURN
    Result
    Bud & Act Var Values = 
VAR CurrentLineNum = SELECTEDVALUE ( Finance[Line Number] )
RETURN
    IF ( 
        CurrentLineNum IN VALUES ( 'Gross margin'[GM] ),
        [Bud & Act Var %],
        [Bud & Act Var]
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      tamerj1  Is still not giving me the correct % value. Can we just make it blank instead of displaying value for the Gross margin rows. Just make the Gross margin row blank. 

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Anonymous 

        Yes

        Bud & Act Var Values = 
VAR CurrentLineNum = SELECTEDVALUE ( Finance[Line Number] )
RETURN
    IF ( 
        CurrentLineNum IN VALUES ( 'Gross margin'[GM] ),
        BLANK ( ),
        [Bud & Act Var]
    )