Forum Discussion
Find Max per category of a countif function
I have the following data. I want to be able to find highest number of times an answer is repeated per species type A,B,C. I have been able to do a countif to find how many times answer is repeated by species type by using the following formula but can't seem to do a max per each of the species category.
I am confused as to what to do.
Expected Result:
| Expected Result | Answer | Number of Times |
| Species Type A | Nitens | 3 |
| Species Type B | Mangium | 2 |
| Species Type C | Conifer | 2 |
Sample Data
| Species | Answer |
| Species Type A | Nitens |
| Species Type A | Nitens |
| Species Type A | Redwood |
| Species Type A | California Mixer |
| Species Type A | Champion Pine |
| Species Type A | Globulus |
| Species Type A | Nitens |
| Species Type A | Pellita |
| Species Type A | Nitens |
| Species Type A | Redwood |
| Species Type B | Fir |
| Species Type B | Mangium |
| Species Type B | Group |
| Species Type B | Mangium |
| Species Type B | Globulus |
| Species Type B | Menziesii |
| Species Type B | Radiata |
| Species Type C | Pine |
| Species Type C | Redwood |
| Species Type C | Conifer |
| Species Type C | Softwood |
| Species Type C | Conifer |
Anonymous
Change the measure to the following, replace VALUES by MIN:Topcountanswer = CALCULATE(MIN('relational OPS_Answers'[Answer]),FILTER('relational OPS_Answers','relational OPS_Answers'[countanswer]=MAX('relational OPS_Answers'[countanswer])))
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Anonymous
You could create a column to calculate the the count each answer for each species.
countanswer = CALCULATE(COUNTROWS('relational OPS_Answers'),ALLEXCEPT('relational OPS_Answers','relational OPS_Answers'[Answer],'relational OPS_Answers'[Species]))Then create the measure to return the top count answers for each speacies.
Topcountanswer = CALCULATE(VALUES('relational OPS_Answers'[Answer]),FILTER('relational OPS_Answers','relational OPS_Answers'[countanswer]=MAX('relational OPS_Answers'[countanswer])))
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.@V-pazhen-msft Thanks very much for that. It works nicely but Species Type C has multiple top answers in my full data. When I do max with species type C included it gives error saying "A table of multiple values was supplied when as they are multiple max values (count for 3 answers is 3 each so they all are highest). Is there anyway to restrict max to bring the first top answer so i don't get this error. Thanks heaps.
Anonymous
Change the measure to the following, replace VALUES by MIN:Topcountanswer = CALCULATE(MIN('relational OPS_Answers'[Answer]),FILTER('relational OPS_Answers','relational OPS_Answers'[countanswer]=MAX('relational OPS_Answers'[countanswer])))
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- amitchandak
Super User
Try to create dense rank and filter
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
