Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GCC
Frequent Visitor

Analysis scenario

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.

Cumulative total tariff points
Any suggestions on how to handle this would be appreciated.
Many thanks
Geoff

1 ACCEPTED 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,

 

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-cherch-msft
Employee
Employee

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))

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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,

 

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Cherie,

This works fantastically well, thank you very much for your help.

Regards

Geoff

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.