Calculate budget with condition in the other table

[Anonymous]

Hi

Hi all,

I am creating a measure to calculate the budegt, I need to use the condition which is Budget Amount =0 in Level 8 table, but the result is wrong. I am thinking probably the relation between Level 8 and Level 9 is many to many and both direction causes the error.

Cost without Budget = CALCULATE(SUM('Account Ledger (Level 9)'[Cost to Date]),  'Account Master (Level 8)'[Revised Budget Amount] = 0)

I really need help to figure it out.

Thank you in advance!

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to your statement, I think there should be multiple [Revised Budget Amount] for same keycolumn in Account Master (Level 8). Keycolumn should be the column which we are using to connect "Account Master (Level 8)" and "Account Ledger (Level 9)". 

Try this code to create a measure.

Measure = 
CALCULATE(
SUM('Account Ledger (Level 9)'[Cost to Date]),
FILTER(ALL('Account Ledger (Level 9)'),
CALCULATE(SUM('Account Master (Level 8)'[Revised Budget Amount]),FILTER(ALL('Account Master (Level 8)'),'Account Master (Level 8)'[Keycolumn] = MAX('Account Ledger (Level 9)'[keycolumn]))) = 0)
)

Here is my sample.

Result is as below.

Best Regards,
Rico Zhou

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

[amitchandak]

@Anonymous ,what error it is giving

Try like

Cost without Budget = CALCULATE(SUM('Account Ledger (Level 9)'[Cost to Date]),Filter( 'Account Master (Level 8)',  'Account Master (Level 8)'[Revised Budget Amount] = 0) )

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.