cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
D_Eichenberger Frequent Visitor
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!

 

PIC.gif

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Ignore values less than zero in calculation

Hi @D_Eichenberger,

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]))))

A.PNG

You can also download the PBIX file to have a view.

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
Community Support Team

Re: Ignore values less than zero in calculation

Hi @D_Eichenberger,

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]))))

A.PNG

You can also download the PBIX file to have a view.

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.
D_Eichenberger Frequent Visitor
Frequent Visitor

Re: Ignore values less than zero in calculation

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