RANKX over two Dimensions

[Anonymous]

Hello all,

I need an idea 🙂

I have an Star Schema Model with one fact table and two dimensions

1. Dim Customer

2. Dim Product

The fact table sum the sales Amount, the fact table are connected to the dimensions.

My goal is to create a Matrix, that shows the TOP10 Customer by the sales amount, and the TOP 5 Products which bought by the customers.

A native TOPN Filter in Power BI Desktop doesn't work, because I can only use one TOP Filter.

For each Dimension a RANKX doest also not work, because I will get the RANKING fromthe customer and can with a less then filter the top 10, but the filter for the product will also not work, because the Product RANK doest not know which customers are selected.

Any ideas?

Thanks,

Philipp

[LivioLanzo · Accepted solution]

@Anonymous 

I guess there're different ways you could go about this. You could use the two below measures for instance, they may vary based on the requirement:

TOP10Cust Sales = 
VAR TOP10Cust =  
        CALCULATETABLE(
            TOPN(
                10,
                VALUES( Customer[CustomerKey] ),
                CALCULATE( SUMX( Sales, Sales[Quantity] * Sales[Unit Price] ) ),
                DESC
            ),
            ALLSELECTED( Customer )
        )
RETURN
CALCULATE(
    SUMX( Sales, Sales[Unit Price] * Sales[Quantity] ),
    KEEPFILTERS( TOP10Cust )
)

TOP 5 Prods of Top 10 Cust = 
VAR TOP10Cust = 
    CALCULATETABLE(
        TOPN(
            10,
            VALUES( Customer[CustomerKey] ),
            CALCULATE( SUMX( Sales, Sales[Quantity] * Sales[Unit Price] ) ),
            DESC
            ),
        ALLSELECTED( Customer )
    )
RETURN
IF(
    HASONEVALUE( Customer[CustomerKey] ),
    IF(
        VALUES( Customer[CustomerKey] ) in TOP10Cust,
        CONCATENATEX(
            TOPN(
                5,
                VALUES( 'Product'[ProductKey]  ),
                CALCULATE( SUMX( Sales, Sales[Quantity] * Sales[Unit Price] ) ),
                DESC
            ),
            'Product'[ProductKey],
            ", "
        )
    )
)