Forum Discussion
New user having trouble with Countif Excel formula conversion
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 |
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.⚡
7 Replies
- parry2k
Super User
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.⚡
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, sofour 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?