Forum Discussion
Need a Dax Measure -Help !!
Lets say I had 200 students with a list of average scores from 0 to 100. There are also two filters, a date filter by week and a second filter for location of student. There was also a condition created that put those average scores in two categories; "high and medium" 35 to 100 and "low" below 35. The students and average scores vary week to week.
Lets say that the following table in PowerBI displays that 140 of those 200 students with their average score in the "high / medium" category for a particular week.
How do I write a measure that will produce only 10% of those names in the "high/medium" category for an output of 14?
I have been trying to find something in the TopN function. Most posts I see are for calculating percentages of columns which is not what I am looking for. I have been researching for over a week and cannot come up with anything.
Any help is appreciated!!!
Best,
Chris C.
Hi cmcgo3 ,
I created some data:
Here are the steps you can follow：
1. Create measure.
Flag = var _count=COUNTX(ALLSELECTED('Table'),'Table'[StudentID]) var _per=_count*0.1 var _rank=RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[average scores])),,DESC) return IF( _rank<=_per,1,0)2. Place [Flag]in Filters, set is=1, apply filter.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
5 Replies
Hi cmcgo3 ,
I created some data:
Here are the steps you can follow：
1. Create measure.
Flag = var _count=COUNTX(ALLSELECTED('Table'),'Table'[StudentID]) var _per=_count*0.1 var _rank=RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[average scores])),,DESC) return IF( _rank<=_per,1,0)2. Place [Flag]in Filters, set is=1, apply filter.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
THANK YOU!, THANK YOU!, THANK YOU !! It works great for me!!!!!
I am trying to add another feature; having some challenges with creating a measure....
Flag =var _count=COUNTX(ALLSELECTED('Scores'),'Scores'[name])var _per=_count*0.30var _rank=RANKX(ALLSELECTED('Scores'),CALCULATE(SUM('Scores'[score])),,DESC)returnIF(_rank<=_per,1,0)Total Students = COUNTROWS(FILTER('Scores','Scores'[Flag]=1))The output for total students is 20; which is correct.How would I write a measure that would capture what type of automobile a student drives based on the filtered total of 20. For example, of those 20 students, 3 students have "Chevrolet" as automobile. What measure would you write to get a total of 3?I have been "stuck" on trying to create this calculation.Best Regards,Chris C.I am trying to add another feature; having some challenges with creating a measure....
Flag =var _count=COUNTX(ALLSELECTED('Scores'),'Scores'[name])var _per=_count*0.30var _rank=RANKX(ALLSELECTED('Scores'),CALCULATE(SUM('Scores'[score])),,DESC)returnIF(_rank<=_per,1,0)Total Students = COUNTROWS(FILTER('Scores','Scores'[Flag]=1))The output for total students is 20; which is correct.How would I write a measure that would capture what type of automobile a student drives based on the filtered total of 20. For example, of those 20 students, 3 students have "Chevrolet" as automobile. What measure would you write to get a total of 3?I have been "stuck" on trying to create this calculation.Best Regards,Chris C.I also wanted to add a table underneath that would give you totals. For this example, Total Students 20, Count of automobile I would want 3 and then percentage of total ...20 students / 3 chevrolet .15 or 15%. Any help would be greatly appreciated !!
