Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

MAX value from filtered matrix column and ranking

Hello everyone, hope you have a great day!


I've been struggling with computing some measures based on returning the MAX value from another measure in the same matrix.

Here is a link to a Google Sheet with formulas for your understanding (I would like to post a .pbix, but I can't) ☹️ Google Table 

Also, for your understanding - ACB is calculated as DISTINCTCOUNT of store IDs. Other measures are simple SUMs or DIVIDEs.


I've already done all columns before "100% ACB potential" and would really appreciate some help with the rest of them (painted orange)😊


Basically what I'm trying to do is:

1. write a measure that uses the max displayed value from "ACB" column (matrix will be filtered, so the SKU list will change) and then use this value in "100% ACB potential" - multiply RoS by this max displayed value (i've found similar topics, but couldn't implement them to my task, because ACB is a DISTINCTCOUNT, or maybe I'm a little unskilled...);

2. after that, I need an additional column "ABC slots", in which all the displayed in the matrix ACBs need to be sorted from largest to smallest independent of what they are in column "ACB"

3. "Potential value" is quite easy - simple multiply RoS by the "ACB slots" , but the problem is in the previous column:(

4. and finally I need to rate the "Potential value" and calculate the diff between this and the ranking by value.


I fully understand that I've wrote quite a chunky message, but you are my last hope. I've been working on this task a load of time, but it seems that i don't have enough skills. I would really appreciate some help with this.


Here is also the same table, just in case.

Rating ValueSKURoSValueVolumeABCPPU100% ACB potentialACB slotsPotential valueFinal ratingDiff
1A7775 832 02085 7657 501685 832 0207 5015 832 02010
13B75330043405 648 2535 5004 141 500211
2C7494 116 75082 5005 500505 614 4993 3462 504 4813-1
3D5721 700 60214 4172 9711074 293 5753 2611 866 5984-1
6E5321 433 57920 0052 695793 990 0853 2571 732 53051
4F4821 489 33815 3543 092973 613 0423 0921 489 3386-2
5G4561 487 0161203 261193 420 4562 9711 354 7767-2
7H256833 7848 0273 2571041 920 2382 695689 9138-1
8I240801 5187 6493 3461021 796 82718043 1189-1
9J18533 283196180501 386 977539 80010-1
10K1377 2714353671 029 066212 88111-1
11L1042 1903682151782 104552112-1
12M884429 857530662 917326513-1
Solution Specialist
Solution Specialist

Hi Alex,


I'd love to try to help you here. However, it sounds like your data model is complex enough that not sharing a pbix is a real handicap to helping you with your problem. Please check out Greg's phenomenal pinned post about helping people help you, in particular the "Post Sample Data" section.

HI, @Wilson_ !

Thank you for your initiative!


Sorry for some delay in my response - had to approve the data with my manager. 
Here you can find the sampled .pbix for my case 😊

The table isn't the same as posted by me above - that's because I've left only non-sensitive data and cutted out some of it, so some formulas and etc. can work not quite properly, but the main used formulas and datasets structures are correct.


And i think this is important - the final version of the table needs to be sorted by "Final rating" column...

Hope I helped you helping me😅


Looking forward for your ideas!! (if there'd be any for my case..🤔)

Solution Specialist
Solution Specialist

Hi Alex,


Thanks for providing the sample pbix. I started to take a look at the data, then came to the realization that there's a bit of circular dependency in your desired result. It seems to me that because you want the "ACB slots" field in a fixed order, how the data is sorted affects the final rating, which affects how the data is sorted, etc.


To elaborate, how the rows are sorted determines which row is associated with each ACB slot, which affects the potential value column, which affects the final rating column, which affects the sorting, which affects which row is associated with each ACB slot.


Does that make sense? Or am I missing something?

Hi, @Wilson_!


Thanks for taking your time for my problem.


I see what you're trying to say - the final table needs to be sorted by two columns at once - by "ACB Slots" and final rating. And final rating is calculated by the participation of the "ACB slots". Good for you (and bad for me) for noticing that. Seems like the best solution for this issue is to use THE good old Excel and forget about flexibility😃


Thank you again and hope you'll have a nice day!

Community Support
Community Support

Hi  @alex_gkk ,

As I checked the data in the shared link, it seems that there are some "fault" in it:

1. The calculation of the field [100% ACB potential]

  • The formula is C3*MAX($F$3:$F$15), and MAX($F$3:$F$15) should return the max ACB: 7501. Then the correct result should be 777*7501=5828277 for the line 3.vyiruanmsft_0-1681718624463.png

2. The value for the field [ACB slots], I think that should display the one as below screenshotvyiruanmsft_1-1681718769766.png

And the rest of the fields are calculated based on the previous 2 fields, so if the algorithm for the previous fields is incorrect, their results should also be incorrect...


I created a sample pbix file(see the attachment) base on my understanding, please check if that is what you want.


Best Regards

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

Hello @v-yiruan-msft !


Thank you a lot for participating in my struggling 🙂

The data is all right - I've doublechecked the formulas before uploading, but there is some incorrect info in my request...


The table itself should be always sorted by the "Final rating column" at the end, and so should be the "ACB slots" - this column needs to return the ACBs that are already in the according column on the left (calc. by DISTINCTCOUNT), but displayed descending as in Google Sheets. And your formula is doing good! but not quiet hits the target 😣 


I've checked your .pbix - thank you a lot!! - and tried to take some of your ideas, but the problem is that i take the data for calculating RoS, ACB and Value/Volume from different tables and they are measures. So, MAX formula for ALLSELECTED isn't working the way it means to. I've tried before to surpass the MAX restriction for column ref. and made a calc. column with my measure for ACB, but then the formula returns the MAX only for the selected SKU, but not all of them that are in the matrix😐

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors