cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 Value SKU RoS Value Volume ABC PPU 100% ACB potential ACB slots Potential value Final rating Diff 1 A 777 5 832 020 85 765 7 501 68 5 832 020 7 501 5 832 020 1 0 13 B 753 300 4 3 40 5 648 253 5 500 4 141 500 2 11 2 C 749 4 116 750 82 500 5 500 50 5 614 499 3 346 2 504 481 3 -1 3 D 572 1 700 602 14 417 2 971 107 4 293 575 3 261 1 866 598 4 -1 6 E 532 1 433 579 20 005 2 695 79 3 990 085 3 257 1 732 530 5 1 4 F 482 1 489 338 15 354 3 092 97 3 613 042 3 092 1 489 338 6 -2 5 G 456 1 487 016 120 3 261 19 3 420 456 2 971 1 354 776 7 -2 7 H 256 833 784 8 027 3 257 104 1 920 238 2 695 689 913 8 -1 8 I 240 801 518 7 649 3 346 102 1 796 827 180 43 118 9 -1 9 J 185 33 283 196 180 50 1 386 977 53 9 800 10 -1 10 K 137 7 271 43 53 67 1 029 066 21 2 881 11 -1 11 L 104 2 190 368 21 51 782 104 5 521 12 -1 12 M 88 442 9 857 5 30 662 917 3 265 13 -1
6 REPLIES 6
Memorable Member

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.

Frequent Visitor

HI, @Wilson_ !

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

Memorable Member

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?

Frequent Visitor

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

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.

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

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.
Frequent Visitor

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😐