IF Formula Calculated Column

[Julius410]

Hi there,

I am trying to create a calculated column that allows me to differentiate between projects that have a budget and projects with no budget. Currently, I am using the following formula:

Budgeted = IF( 'Impacts Final'[Budget] = 0, "Budget N/A", "Budgeted")

Unfortunately, Power BI does not display the desired outcome. It displays the correct classification of budgeted and non-budgeted projects. It does not display forecasted or actual values for the "Budgeted" projects. However, it does display actual and forecasted values for projects with "Budget N/A". Please see screenshot below:

Can someone help me to solve this issue?

Thank you.

Julius

[Julius410]

Hi @Anonymous 

Thanks so much for your extensive reply and the detailed explantion.

Your solution is getting close. However, it's still not exactly what I wanted. The problem is that the number initiatives stay the same regardless of which filter is selected. Please see below the screenshot for slicer "Budgeted":

Please see below screenshot for slicer "Budget N/A":

I would like the initiatives that do not have a budgeted value against them filtered out when I select "Budget N/A".

Do you think that this is possible?

Thanks.

Julius

[amitchandak]

@Julius410 , Not very clear,

You can have

Budgeted value 1= IF( 'Impacts Final'[Budget] = 0, [Budget], [Forecast])

Budgeted value 2= IF( 'Impacts Final'[Budget] = 0, [Budget], [Actual])

[Julius410]

Hi @amitchandak,

Thanks for your reply but this doesn't fix my issue. I'll try to explain it better.

I need a slicer that allows me to differentiate between projects which have a budget (>0) and projects which do not have a budget (<=0 or =BLANK()). When I select the filter for budgeted projects it should still display the data that sits in in the Actual and Forecast column. Currently, this is not the case.

Any idea how I can achieve that?

Thank you.

Julius

[Anonymous · Accepted solution]

Hi @Julius410 ,

You could create a dynamic measure to display the budget value.

Before you create the measure, create a table with the following values for the slicer.

You can create it by entering data.

Then create the measure.

Budget value =
SWITCH (
    SELECTEDVALUE ( 'For Slicer'[Budgeted] ),
    "Budgeted", CALCULATE ( SUM ( 'Table'[Budget] ), FILTER ( 'Table', [Budget] > 0 ) ),
    "Budget N/A", BLANK ()
)

 Here's the results. When you select 'Budgeted' in the slicer,

When you select 'Budget N/A' in the slicer,

Hope to help you.

Best Regards,

Stephen Tao

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