cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Add IF not zero to calculated measure (while dividing)

Hi,

how could I add a filter so that sum of cost is not 0 to this calculated measure?

Rev per click = sum(GA[Cost])/sum(GA[Clicks])

I want it to make calculations only if s sum of cost is more than 0.

Thnx!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

That is because you are having a scenario where the sum of GA[Clicks] is zero and the measure is attempting to divide by 0.

How you want to handle this depends on your report.

Usually you want to have your data setup so that you do not get div by zero.

```Rev per click =
VAR CostSum =
SUM ( GA[Cost] )
VAR ClickSum =
SUM ( GA[Clicks] )
RETURN
IF (
ClickSum = 0
|| ISBLANK ( ClickSum ),
ERROR ( "Div by 0" ),
IF ( CostSum > 0, CostSum / ClickSum, BLANK () )
)```

However if div by zero is a normal case then you can use the divide function to replace the errors by blanks

`Rev per click =VAR CostSum = SUM ( GA[Cost] )RETURN IF ( CostSum > 0, DIVIDE ( CostSum, SUM ( GA[Clicks] ), BLANK () ), BLANK () )`

4 REPLIES 4
Anonymous
Not applicable

If all you want is to not see values less than 0 then this will do what you want

```Rev per click =
VAR CostSum =
SUM ( GA[Cost] )
RETURN
IF ( CostSum > 0, SUM ( CostSum ) / SUM ( GA[Clicks] ), BLANK () )```

Frequent Visitor

Thnx, I did as you told me, but it doesnt work anyway, shows to me the same error

Anonymous
Not applicable

That is because you are having a scenario where the sum of GA[Clicks] is zero and the measure is attempting to divide by 0.

How you want to handle this depends on your report.

Usually you want to have your data setup so that you do not get div by zero.

```Rev per click =
VAR CostSum =
SUM ( GA[Cost] )
VAR ClickSum =
SUM ( GA[Clicks] )
RETURN
IF (
ClickSum = 0
|| ISBLANK ( ClickSum ),
ERROR ( "Div by 0" ),
IF ( CostSum > 0, CostSum / ClickSum, BLANK () )
)```

However if div by zero is a normal case then you can use the divide function to replace the errors by blanks

`Rev per click =VAR CostSum = SUM ( GA[Cost] )RETURN IF ( CostSum > 0, DIVIDE ( CostSum, SUM ( GA[Clicks] ), BLANK () ), BLANK () )`

Frequent Visitor

yes, got it, thnx a lot!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors