Forum Discussion

Julius410's avatar
Julius410
Frequent Visitor
4 years ago
Solved

IF Formula Calculated Column

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
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Julius410
      Frequent Visitor
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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.

  • Julius410's avatar
    Julius410
    Frequent Visitor
    4 years ago

    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