Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm relatively new to Power BI and am struggling with an analysis scenario and wondering if anyone has a suggestion on how to handle it.
I've detailed my calculation steps below which are based on sample data. The real data has thousands of records for each year.
Any suggestions on how to handle this would be appreciated.
Many thanks
Geoff
Solved! Go to Solution.
Hi @GCC
You may try below measures.
Step1 = COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[School]=MAX('Table'[School])))
Step3_rank = RANKX(FILTER(ALLSELECTED('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[School]=MAX('Table'[School])),CALCULATE(SUM('Table'[cum_total_tariff_pts])),,ASC,Skip)
Lowest 20% avg = CALCULATE(AVERAGE('Table'[cum_total_tariff_pts]),FILTER(ALLSELECTED('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[School]=MAX('Table'[School])&&[Step3_rank]<=[Step1]*0.2))
Middle 60% avg = CALCULATE(AVERAGE('Table'[cum_total_tariff_pts]),FILTER(ALLSELECTED('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[School]=MAX('Table'[School])&&[Step3_rank]<=[Step1]*0.2+[Step1]*0.6&&[Step3_rank]>[Step1]*0.2))
Top 20% avg = CALCULATE(AVERAGE('Table'[cum_total_tariff_pts]),FILTER(ALLSELECTED('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[School]=MAX('Table'[School])&&[Step3_rank]>[Step1]*0.2+[Step1]*0.6))
Regards,
Hi @GCC
You may try to create measures as below:
Step1 = COUNTROWS(ALLEXCEPT('Table','Table'[Year]))
Step3_rank = RANKX(ALL('Table'),CALCULATE(SUM('Table'[cum_total_tariff_pts])),,ASC,Skip)
Lowest 20% avg = CALCULATE(AVERAGE('Table'[cum_total_tariff_pts]),FILTER(ALLEXCEPT('Table','Table'[Year]),[Step3_rank]<=[Step1]*0.2))
Middle 60% avg = CALCULATE(AVERAGE('Table'[cum_total_tariff_pts]),FILTER(ALLEXCEPT('Table','Table'[Year]),[Step3_rank]<=[Step1]*0.2+[Step1]*0.6&&[Step3_rank]>[Step1]*0.2))
Top 20% avg = CALCULATE(AVERAGE('Table'[cum_total_tariff_pts]),FILTER(ALLEXCEPT('Table','Table'[Year]),[Step3_rank]>[Step1]*0.2+[Step1]*0.6))
Regards,
Hi Cherie,
Thank you very much for creating this code for me and it works just perfect as long as you're working with just one year. I have data for numerous years and numerous schools within these years. I need to be able to calulate the lowest 20% average, mid 60% average and top 20% average for each school within each year. Could you help me with creating this code? I've tried using your code with slicers but it didn't work.
Thanks once again for your help.
Regards
Geoff
Hi @GCC
You may try below measures.
Step1 = COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[School]=MAX('Table'[School])))
Step3_rank = RANKX(FILTER(ALLSELECTED('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[School]=MAX('Table'[School])),CALCULATE(SUM('Table'[cum_total_tariff_pts])),,ASC,Skip)
Lowest 20% avg = CALCULATE(AVERAGE('Table'[cum_total_tariff_pts]),FILTER(ALLSELECTED('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[School]=MAX('Table'[School])&&[Step3_rank]<=[Step1]*0.2))
Middle 60% avg = CALCULATE(AVERAGE('Table'[cum_total_tariff_pts]),FILTER(ALLSELECTED('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[School]=MAX('Table'[School])&&[Step3_rank]<=[Step1]*0.2+[Step1]*0.6&&[Step3_rank]>[Step1]*0.2))
Top 20% avg = CALCULATE(AVERAGE('Table'[cum_total_tariff_pts]),FILTER(ALLSELECTED('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[School]=MAX('Table'[School])&&[Step3_rank]>[Step1]*0.2+[Step1]*0.6))
Regards,
Hi Cherie,
This works fantastically well, thank you very much for your help.
Regards
Geoff
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |