Forum Discussion

gianmarco's avatar
gianmarco
Icon for Helper IV rankHelper IV
2 years ago
Solved

Wrong formula in calculated column

Dear All,

I have the following calculated table:

 

This works as long as I have at least a value which is not only 0 in "Cashflow[Scenario]".

In fact, in this example, I have values 0 and 1 as values in "Cashflow[Scenario]".

 

Problem is this formula doesn't return anything if I have only value 0 in "Cashflow[Scenario]".

In this case I would like the formula to return the following:

ValueScenario
01

 

Can you suggest me something?
Thank you a lot to the whole community

gianmarco

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi gianmarco ,

    Please have a try.

    Max Scenario = MAX(Cashflow[Scenario]) // create measure for max scenario

    Calculated Table = 
FILTER( // filter out blank or zero values
    ADDCOLUMNS( // add new column for scenario
        Cashflow,
        "Scenario", IF(Cashflow[Scenario] = 0, [Max Scenario], Cashflow[Scenario]) // replace 0 with max scenario
    ),
    NOT(ISBLANK(Cashflow[Value])) && Cashflow[Value] <> 0
)

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

     

     

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    You are filtering too soon. Do the summarize first and then check your scenarios.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi gianmarco ,

    Please have a try.

    Max Scenario = MAX(Cashflow[Scenario]) // create measure for max scenario

    Calculated Table = 
FILTER( // filter out blank or zero values
    ADDCOLUMNS( // add new column for scenario
        Cashflow,
        "Scenario", IF(Cashflow[Scenario] = 0, [Max Scenario], Cashflow[Scenario]) // replace 0 with max scenario
    ),
    NOT(ISBLANK(Cashflow[Value])) && Cashflow[Value] <> 0
)

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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