New user having trouble with Countif Excel formula conversion

[Anonymous]

I am new to Power BI and am having difficulty with the following countif formula in Excel within Power BI. The formula is in the Req column,  =1/countif(M:M,M2), and then a filldown function to add the formula to each row.  Here is a snippet of the table.  I have read in forums that is suggested to use Calculate, but I am not understanding how to translate this for my particular scenario. Thanks in advance for any assistance. 

J	K	L	M
1	Slide Count	Req	AccessionFix
2	1	.25	DG12345
3	1	.25	DG12345
4	1	.25	DG12345
5	1	.25	DG12345
6	1	1	TE23456
7	1	.33	PW2323
8	1	.33	PW2323
9	1	.33	PW2323
10	1	.50	OP1234
11	1	.50	OP1234

[parry2k · Accepted solution]

@Anonymous try this measure

Measure = 
DIVIDE ( 1, CALCULATE( COUNTROWS ( Table ), ALLEXCEPT ( Table, Table ( [ColumnM] ) ) )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

THis is how I translated your recommended formula, 

Req = DIVIDE ( 1, CALCULATE( COUNTROWS ( exportdata ), ALLEXCEPT ( exportdata, exportdata[FixedAccession])))

It doesnt appear to be returning the expected values.

So if I have this Req listed 4 times in the column, ie DE1234, each occurence would be .25, so

four occurences of DE1234 = 1 total req count.

Make sense?

[parry2k]

@Anonymous did you add my expression as a measure when you put it in the table visual what is not working, can you be more specific? Are you getting an error? Wrong result? What is it? How I will know?

[Anonymous]

@parry2kmy apologies, new to this. I am not getting expected results. Let me try again by creating a measure. 

[parry2k]

@Anonymous no worries, take your time, if you run into an issue, share pbix file with sample data and remove sensitive info before sharing, you can share using one drive/google drive or send directly to my email address given in the footer section.

[Anonymous]

@parry2kI discovered that the reason the output wasnt looking correct was i had an incorrect format for the column for some reason. Once I corrected that, that gave the output I was looking for . Just an awesome prompt response to my  query. Thanks so much for the help!

[Anonymous]

Thank  you!