cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Ignore values less than zero in calculation

Hi

I just started with Power-BI and I am struggeling with something I could solve in Excel in notime -.-

I have a colum (Act_ATSys_OI) where the weekly bookings get calculated from another table and another one (Act_ATSys_CummOI) where I want to summarize them as a YTD figure. Now I managed to do that, but I do not want the Formula in column "Act_ATSys_CummOI" to extend further than there is a value above 0 in column "Act_ATSys_OI". - See screenshot below where the last cells constantly show "8'846".

My formula so far:

Act_ATSys_CummOI = CALCULATE (
SUM (Actual_Calc[Act_ATSys_OI]) ; (Actual_Calc[Act_ATSys_OI]) >0;
FILTER ( ALL (Actual_Calc); Actual_Calc[Act_Week] <= EARLIER (Actual_Calc[Act_Week]))
)

I tried to exclude them with an IF statment, but I faild. Any help would be highly appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Ignore values less than zero in calculation

Based on my test, you can refer to below steps:

You can create a column to calculate your value and you can see the result.

Total running = IF([Act_ATSys_OI]<>0,CALCULATE(SUM(Sheet1[Act_ATSys_OI]),

FILTER(ALL(Sheet1),'Sheet1'[Act_week]<=EARLIER(Sheet1[Act_week]))))

https://www.dropbox.com/s/5pb0de4u4emodqa/Ignore%20values%20less%20than%20zero%20in%20calculation.pb...

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team

## Re: Ignore values less than zero in calculation

Based on my test, you can refer to below steps:

You can create a column to calculate your value and you can see the result.

Total running = IF([Act_ATSys_OI]<>0,CALCULATE(SUM(Sheet1[Act_ATSys_OI]),

FILTER(ALL(Sheet1),'Sheet1'[Act_week]<=EARLIER(Sheet1[Act_week]))))

https://www.dropbox.com/s/5pb0de4u4emodqa/Ignore%20values%20less%20than%20zero%20in%20calculation.pb...

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Ignore values less than zero in calculation

You made my day! Thanks a lot, works like a charm.