Pivot table with ranking buckets

[gazpage]

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.

[TABLE="width: 300"]
<tbody>[TR]
[TD]Ranking[/TD]
[TD]Measure1[/TD]
[TD]Measure 2 etc[/TD]
[/TR]
[TR]
[TD]1-10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11-25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26-50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 300"]
<tbody>[TR]
[TD]Amount[/TD]
[TD]Measure1[/TD]
[TD]Measure 2 etc[/TD]
[/TR]
[TR]
[TD]1-50k[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50k-100k[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100k-500k[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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?

 

[Matt Allington]

This may help. Dynamic Segmentation – DAX Patterns

 

[gazpage]

Well that certainly seems to be it for my second case. Will have to see if I can adapt it to also do ranking; that may be beyond my skill level.

Thanks a lot.

 

[gazpage]

The ClusteredSales: example was exactly what I needed for the second table. Many thanks for pointing it out.

Any idea how to convert that to my first requirement which is to filter it be ranking buckets? I guess I need a new disconnected table and to change the calculation of "CustomerSales" from the ADDCOLUMNS, but not sure I know how to make that a rank.

 

[gazpage]

In case anyone has the same issue, this seems to work.

CALCULATE (
    SUM ( UnitTape[Discounted Bank Value] ),
    FILTER(
            ADDCOLUMNS (
                'Properties',
                "RankValue",
                RANKX ( 
                    ALLSELECTED ( 'Properties' ),
                    CALCULATE ( SUM ( UnitTape[Discounted Bank Value] ) )
                )
            ),
            COUNTROWS (
                FILTER (
                    RankSegment,
                    [RankValue] >= RankSegment[Min]
                    && [RankValue] <= RankSegment[Max]
                )
        ) > 0
    )
)

My column names are different, but it's the same pattern.