cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pingi14
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

 

Example.png

 

I hope the picture and that someone can assist

 

Thanking you
Stephen Daff

 

1 REPLY 1
Anonymous
Not applicable

[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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.