Goal seek / add total of column to an unique row - DAX

[Gonqq]

Hi. So i have a simple matrix with different periods and a balance sheet. 

What i want is basically a goal seek: IF the total <> 0 , then add the value to this account row: Consolidado[Conta SAP] = 2610200100.

With this example: the 15.400.538 would add to the row where [Conta SAP] = 2610200100 is thus turning  total = 0.

Thanks in advance

[Anonymous · Accepted solution]

Hi  @Gonqq ,

Here are the steps you can follow：

1. Create measure.

Measure =
CALCULATE(SUM('financials'[Profit]),FILTER(ALL(financials),'financials'[Segment]=MAX('financials'[Segment])&&'financials'[Month Number]=MAX('financials'[Month Number]))
)

Flag =
var _1=CALCULATE(SUM('financials'[Profit]),FILTER(ALL(financials),'financials'[Segment]=MAX('financials'[Segment])&&'financials'[Month Number]=MAX('financials'[Month Number]))
)
VAR _all =
CALCULATE(SUM('financials'[Profit]),FILTER(ALL(financials),'financials'[Month Number]=MAX('financials'[Month Number])))
return
IF(
ISINSCOPE('financials'[Segment])&&MAX('financials'[Segment])<>"Enterprise",_1,
IF(
    'financials'[Measure]<>0&&MAX('financials'[Segment])<>"Enterprise",0, _1+_all
))

2. Result:

Please click here for the pbix file

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Gonqq ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Gonqq]

Hi @amitchandak , here's the link to the .pbix file:

Goal Seek Question PBI.pbix

Thanks