DAX Issue continue

[Anonymous]

Hi Guys,

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

[tamerj1 · Accepted solution]

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 · Accepted solution]

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]

@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 · Accepted solution]

@Anonymous 

Yes

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

[amitchandak]

@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