topic Re: DAX Issue continue in DAX Commands and Tips

DAX Issue continue

Anonymous — Tue, 03 May 2022 18:33:21 GMT

Hi Guys,

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

Re: DAX Issue continue

amitchandak — Wed, 04 May 2022 01:23:16 GMT

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

Re: DAX Issue continue

tamerj1 — Wed, 04 May 2022 14:13:17 GMT

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

Re: DAX Issue continue

Anonymous — Wed, 04 May 2022 14:56:46 GMT

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.

Re: DAX Issue continue

tamerj1 — Wed, 04 May 2022 15:41:04 GMT

Anonymous

Yes

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