Count Values with condition

[vincenardo]

In my data set, I have multiple rows for each 'Name' when each 'Name; has a Skill:Name assigned to them, with a skill level, skill start date and skill end date;

The end result I am trying to get to is to find out how many 'names' we have for each Skill:Name in the data set. In the above example, 'Calvin Lip' has 3 skills (Costco, Reface Sales Appt, Costco Bath - Sales Appt, and Bath Solutions Sales Appt). Becuase he has 4 skills, he would count as .25 in each (Count of Skill:Name by Name / 1).

The other caveat to the formula, is I only want to include the Skill:Name where the end date is NULL (IE: the skill is active) and where the skill level is NOT 1, as in this data set example;

For Kevin, he would count as .50 in each (not counting the skill with an end date nor the skill level of 1.

Any suggestions are appreciated!

[Ashish_Mathur · Accepted solution]

Hi,

Try these calculated column formulas

Count with condition = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Name]=EARLIER(Data[Name])&&Data[Skill End Date]=BLANK()&&Data[Skill Level]<>1))

Apportioned count = if(and(Data[Skill End Date]=BLANK(),Data[Skill Level]<>1),DIVIDE(1,Data[Count with condition]),BLANK())

Hope this helps.

[Ashish_Mathur]

Hi,

So For Calvin Lip, you want to see 0.25 in another column (in each of the 4 rows) and for Kevin, you want to see null, 0.5, 0.5 and null.  Am i correct?  Share the download link of the PBI file.

[vincenardo]

Yes, that is correct. Here is a link to a sample file.

https://docs.google.com/spreadsheets/d/1xEn2qfMT2IZdbLw1sFSwsCnHlMH2NRQT/edit?usp=share_link&ouid=11...

[Ashish_Mathur · Accepted solution]

Hi,

Try these calculated column formulas

Count with condition = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Name]=EARLIER(Data[Name])&&Data[Skill End Date]=BLANK()&&Data[Skill Level]<>1))

Apportioned count = if(and(Data[Skill End Date]=BLANK(),Data[Skill Level]<>1),DIVIDE(1,Data[Count with condition]),BLANK())

Hope this helps.

[vincenardo · Accepted solution]

Thank you! This work perfectly!!

[Ashish_Mathur]

You are welcome.