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|
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..🤔)
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?
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!
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]
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.
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😐
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
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!
Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.