MAX value from filtered matrix column and ranking

[alex_gkk]

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

[Wilson_]

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.

[alex_gkk]

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

[Wilson_]

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?

[alex_gkk]

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!

[v-yiruan-msft]

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

[alex_gkk]

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😐