Hi,
I'm trying to create a pivot table based on Power Pivot where the rows show two things. Ranking Buckets and Amount Buckets, so something like this.
<tbody>
</tbody>
<tbody>
</tbody>
The challenge I am finding is twofold.
1) I would normally add a calculated column to allow me to filter as shown on the right. However, then the rankings are not dynamic for any filters / slicers etc. For example I might have Company X as the number 1 ranked company, but if you slice out US sales then its rank 50; if that company has a 1 in the calculated column it stays as 1.
2) I'm actually ranking buckets already. The number 1 rank is not the highest valued row, but all the rows belonging to the bucket whose rows sum to the highest value.
Any suggestions?
I'm trying to create a pivot table based on Power Pivot where the rows show two things. Ranking Buckets and Amount Buckets, so something like this.
Ranking | Measure1 | Measure 2 etc |
1-10 | ||
11-25 | ||
26-50 | ||
etc |
<tbody>
</tbody>
Amount | Measure1 | Measure 2 etc |
1-50k | ||
50k-100k | ||
100k-500k | ||
etc |
<tbody>
</tbody>
The challenge I am finding is twofold.
1) I would normally add a calculated column to allow me to filter as shown on the right. However, then the rankings are not dynamic for any filters / slicers etc. For example I might have Company X as the number 1 ranked company, but if you slice out US sales then its rank 50; if that company has a 1 in the calculated column it stays as 1.
2) I'm actually ranking buckets already. The number 1 rank is not the highest valued row, but all the rows belonging to the bucket whose rows sum to the highest value.
Any suggestions?