Solved: Conditional Count

Anonymous · ‎04-27-2022

Consider the below table as sample data

FY	Dept
Q1 FY23	abc
Q1 FY23	def
Q1 FY23	ghi
Q1 FY24	jkl
Q1 FY24	bcd
Q1 FY24	mno
Q1 FY25	def
Q1 FY25	pqr
Q1 FY25	xyz
Q1 FY26	ghi
Q1 FY26	efg
Q1 FY26	ijk

A measure needs to be created to count the DC according to year.

Condition- If a DC is already counted in any FY , then it shouldn't be counted again.

v-yalanwu-msft · ‎05-01-2022

Hi, @Anonymous ;

Try it.

Count = 
var _pre=SUMMARIZE(FILTER(ALL('Table'),[FY ]<MAX('Table'[FY ])),[Dept])
return 
CALCULATE(COUNT('Table'[Dept]),FILTER(ALL('Table'),[FY ]=MAX('Table'[FY ])&&NOT( [Dept] ) in _pre))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

How to get your questions answered quickly -- How to provide sample data

View solution in original post

Ashish_Mathur · ‎05-01-2022

Hi,

Please show the end result that you are expecting.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

v-yalanwu-msft · ‎05-01-2022

Hi, @Anonymous ;

Try it.

Count = 
var _pre=SUMMARIZE(FILTER(ALL('Table'),[FY ]<MAX('Table'[FY ])),[Dept])
return 
CALCULATE(COUNT('Table'[Dept]),FILTER(ALL('Table'),[FY ]=MAX('Table'[FY ])&&NOT( [Dept] ) in _pre))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

How to get your questions answered quickly -- How to provide sample data