cancel
Showing results for
Did you mean:
Frequent Visitor

## Equivalent to CountIF Excel Function

Good afternoon

I can never seem to get this right.  I have a long listing of sales figures, where I am wanting to a CountIf on the the sales amounts that have an offset negative amount for the same customer

I hope the picture and that someone can assist

Thanking you
Stephen Daff

Super User

## Re: Equivalent to CountIF Excel Function

```[Qty Offsets] = -- calculated column, not a measure
var __customer = T[Customer Number]
var __amount = T[Sales Amount]
var __invNo = T[Invoice Number]
var __qtyOffsetsForAmountNotZero =
countrows(
filter(
T,
T[Customer Number] = __customer,
T[Sales Amount] = -__amount
)
) + 0 -- This is needed to force BLANK into 0
var __qtyOffsetsForAmountEquaToZero =
countrows(
filter(
T,
T[Customer Number] = __customer,
T[Sales Amount] = 0,
T[Invoice Number] <> __invNo
)
) + 0
var __qtyOffsets =
if(
__amount <> 0,
__qtyOffsetsForAmountNotZero,
__qtyOffsetsForAmountEquaToZero
)
return
__qtyOffsets

-- Bear in mind that if the amount is 0
-- then we have to make sure that the
-- invoice number is not equal to the
-- current invoice number. This, of course,
-- is not required if the amount <> 0.```

Best

Darek

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 35 members 1,069 guests
Recent signins: