Conditional calculated column issue

[PNicholsonRS]

Hi all,

I have an issue where I need to create a new calculated column from the below data that will show when an item has been billed (i.e. Billed Amt is not blank) and collected. The problem I have is that the data in Collected Amt automatically shows a value when there is data in the Billed Amt column, whether that bill has been collected or not!

This is a problem because if a value shows in Billed Amt, and zero shows in Collected Amt, this could mean that either zero has been collected yet or that all of the bill has actually been written down or off (i.e. Written Off > 0).

Is there any way to create a new calculated column which will only show the value if the Billed has actually been Collected? The logic should be the below:

- Billed and Collected YES = Billed Amt is not blank, Collected Amt > 0

OR

- Billed and Collected YES = Billed Amt is not blank, Collected Amt = 0 but either WUWD Amt = Billed Amt, or Written Off > 0

Any help greatly apprecited!

Thanks!

[MorrisBI]

Hi @PNicholsonRS 

Here is a solution, you need a custom colum, there is a conditional colum but is limited to a fix parameters. So, once you click on custom colum you could insert the next formula or modify it as you wish.

I used just the billed and collecet logic, without the WUWD but is the same.

The steps:

1.- Set your screen to the Data panel.

2.- Right click on the table that you are trying to add your column and then select "Edit Query" (Should be in the name of the table, not the fields, otherway you won't see the option "Edit Query")

3.- Once you are on the Power Query screen, clic on Add Column.

4.- Then, enter the next formula:

Here is the formula in text:

if [Billed] > 0 and [Collected] > 0
then [Billed]
else "No billed/collected"

5.- Finally, the results:

Hope this could solve your issue, if so don't hesitate to mark as solved if not please reply with your comments.

Have a nice day 🙂

[ERD · Accepted solution]

Hi @PNicholsonRS ,

YourColumn =
SWITCH (
    TRUE (),
    NOT ( ISBLANK ( Table[Billed Amt] ) ) && Table[Collected Amt] = 0
        && ( Table[WUWD Amt] = Table[Billed Amt] || Table[Written Off] > 0 ), "Yes",
    NOT ( ISBLANK ( Table[Billed Amt] ) ) && Table[Collected Amt] > 0, "Yes",
    "No"
)

[MorrisBI]

I think this could work as well. With my data example just need to modify a little bit to include the double $0 escenario (Billed and Collected), but I guess this won't happen on the real life.