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