Forum Discussion
Dynamic Index Column in granular matrix
Dear community,
I have a question and I hope you can help me out with this problem.
Assuming I have the following data set. The data set shows for a random supermarket prices for their offered drinks for different brands and also the prices from previous year.
| Index | Drink | Brand | Actual Price in EUR | Price Previous Year in EUR | Order of Importance by Drink |
| 1 | Water | Brand A | 2 | 1 | 1 |
| 2 | Beer | Brand B | 3 | 3 | 6 |
| 3 | Wine | Brand C | 5 | 6 | 7 |
| 4 | Softdrink | Brand B | 2 | 2,5 | 5 |
| 5 | Orange Juice | Brand B | 2 | 1,5 | 3 |
| 6 | Water | Brand C | 1,5 | 1,5 | 1 |
| 7 | Beer | Brand A | 2 | 2 | 6 |
| 8 | Wine | Brand B | 4 | 4 | 7 |
| 9 | Softdrink | Brand D | 2,5 | 2 | 5 |
| 10 | Orange Juice | Brand D | 3 | 3 | 3 |
| 11 | Lemonade | Brand E | 2 | 2 | 4 |
| 12 | Apple Juice | Brand F | 2,5 | 3 | 2 |
| 13 | Apple Juice | Brand D | 3 | 2 | 2 |
| 14 | Orange Juice | Brand A | 3,5 | 3 | 3 |
| 15 | Lemonade | Brand B | 2,5 | 2 | 4 |
| 16 | Beer | Brand E | 4 | 3 | 6 |
| 17 | Wine | Brand F | 4,5 | 5 | 7 |
| 18 | Lemonade | Brand C | 2,5 | 2,5 | 4 |
| 19 | Water | Brand B | 1 | 1,5 | 1 |
| 20 | Water | Brand E | 1,5 | 1,5 | 1 |
What I want to achieve is following scenario:
Each type of drink (water, beer…) has an order of importance and accordingly I want to sort it in a table matrix. E.g. for Water the Order of Importance is 1, for Beer is 6. Now I want to create a matrix table which display the drinks on first level and then on the more granular level for each brand, which is also not a problem. But my final goal would be to create a dynamic number (the red numbers in the screenshot) in front of the drink to display its current position like it is shown below.
At first, my approach was to combine the column “Drink” and column “Order of importance” which would work in a non-dynamic scenario. But e.g. if I now have a different data set for a supermarket which does not sell juices (Order of importance “2” and “3”) then I want the other drinks to move up in the list (Screenshot 3).
Any idea how to achieve this?
Hi Anonymous ,
Please have a try.
Measure_2 = VAR _countdrinks = CALCULATE ( DISTINCTCOUNT ( 'Table without'[Drink] ), ALL ( 'Table without' ) ) VAR _maxcountdrinks = MAXX ( ALLSELECTED ( 'Table without' ), _countdrinks ) VAR _yuanben = MAX ( 'Table without'[Order of Importance by Drink] ) VAR _maxvalue = CALCULATE ( MAX ( 'Table without'[Order of Importance by Drink] ), ALL ( 'Table without' ) ) VAR _rankx = RANKX ( ALLSELECTED ( 'Table without' ), CALCULATE ( MAX ( 'Table without'[Order of Importance by Drink] ) ), , DESC, DENSE ) RETURN IF ( _maxcountdrinks <> _maxvalue, _rankx, _yuanben )If I have misunderstood your meaning, please provide your desired output with more details.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi Anonymous ,
Please refer to my pbix file to see if it helps you.
Create a measure.
Measure = IF( ISERROR( SEARCH("Juice",MAX('Table'[Drink])) ), 1, BLANK() )Then filter the measure.
f I have misunderstood your meaning, please provide your desired output with more details and you sample pbix file without privacy information.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous thank you for your reply.
I think I might misexplained my desired goal.
I dont want to exclude values which have "Juice" in my dateset. Instead I am looking for a way to dynamically order my table/matrix in accordance to the column "Order of Importance by Drink". To archieve this step I could "Sort by column" and then that would do the trick in my opinion. But additionally - and thats the hard part - I want to add dynamic numbers in front of each drink according to its current ranking ("Order of Importance by Drink"). Therefore my first approach was to combine column "Order of Importance by Drink" and column "Drink". But this would work only if all Drinks are included in this data set. So if have now a smaller dataset where the value "Orange Juice" does not exist at all, then all other drinks will stated still the same number in fron of their drink. I tried to show that in my "Not desired Output" stated below in the picture. For the drinks "Water" and "Apple Juice" is is correct ("1. Water" and "2. Apple Juice") but afterwards is the gap as "3. Orange Juice" is not part of the dataset and the numbers would be wrong ("4. Lemonade" is wrong but instead "3. Lemonade" would be correct). So what I want to archieve is that in case certain drinks are missing the number in front of the drink will be continiously in accordance to their order of the existing data. This is stated and marked in green on my desired output table.
I also want to mention the dataset is only an example, but I am searching for a general approach which can be adapted to other data sets as well.
Thanks in adance!
Hi Anonymous ,
Please have a try.
Measure_2 = VAR _countdrinks = CALCULATE ( DISTINCTCOUNT ( 'Table without'[Drink] ), ALL ( 'Table without' ) ) VAR _maxcountdrinks = MAXX ( ALLSELECTED ( 'Table without' ), _countdrinks ) VAR _yuanben = MAX ( 'Table without'[Order of Importance by Drink] ) VAR _maxvalue = CALCULATE ( MAX ( 'Table without'[Order of Importance by Drink] ), ALL ( 'Table without' ) ) VAR _rankx = RANKX ( ALLSELECTED ( 'Table without' ), CALCULATE ( MAX ( 'Table without'[Order of Importance by Drink] ) ), , DESC, DENSE ) RETURN IF ( _maxcountdrinks <> _maxvalue, _rankx, _yuanben )If I have misunderstood your meaning, please provide your desired output with more details.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.