Rickmaurinus
New Member
- Joined
- Sep 25, 2017
- Messages
- 34
- Platform
- Windows
Dear Excel gurus,
Recently I've build a dashboard to show the performance of our stores. Data has been put in the data model, and I made measures in power pivot (DAX) to measure performance.
To dynamically rank my stores in Excel I've used the CubeSet and CubeRankedMember functions in excel. The ranking is based on a Measure that shows the percentage of the target that is reached in a week. This has worked very well for me up to now. Yet this week I found out, that when stores have negative sales (products being returned), and the percentage of target that is reached is negative, my ranking isn't working anymore.
Attached you can find the example workbook with a more elaborate explanation. It has sales data for 10 stores, with 2 managers and 2 weeks of data. You can use the slicer to select the right week with data. In the first week of 2017, the ranking is correct. Yet in week two of 2017, it shows 4 stores of manager 1 (rank 1-4), then has 5 empty cells (ranked 5-9) and lastly shows the store number 9 with negative sales of manager 1 (rank 10).
My challenge is to rank store 9 as 5th, and not show empty cells. Any help would be appreciated. I'm so close to the solution!
https://www.dropbox.com/s/itgt08w4xcsviaq/Casus 1.xlsx?dl=0
With kind regards,
Rick de Groot
Recently I've build a dashboard to show the performance of our stores. Data has been put in the data model, and I made measures in power pivot (DAX) to measure performance.
To dynamically rank my stores in Excel I've used the CubeSet and CubeRankedMember functions in excel. The ranking is based on a Measure that shows the percentage of the target that is reached in a week. This has worked very well for me up to now. Yet this week I found out, that when stores have negative sales (products being returned), and the percentage of target that is reached is negative, my ranking isn't working anymore.
Attached you can find the example workbook with a more elaborate explanation. It has sales data for 10 stores, with 2 managers and 2 weeks of data. You can use the slicer to select the right week with data. In the first week of 2017, the ranking is correct. Yet in week two of 2017, it shows 4 stores of manager 1 (rank 1-4), then has 5 empty cells (ranked 5-9) and lastly shows the store number 9 with negative sales of manager 1 (rank 10).
My challenge is to rank store 9 as 5th, and not show empty cells. Any help would be appreciated. I'm so close to the solution!
https://www.dropbox.com/s/itgt08w4xcsviaq/Casus 1.xlsx?dl=0
With kind regards,
Rick de Groot
