Double TopN Measure Returning Text

Hi Anonymous ,

 

So far, I have completed your first request, and I have encountered some difficulties with your second request. Please wait patiently until I come back next week during work hours. I will continue to study.

 

The measure is

top 5 Accounts per GPU sold = 
VAR _RANK =
    RANKX (
        ALL ( 'Sales(Product Level)' ),
        CALCULATE (
            SUM ( 'Sales(Product Level)'[Quantity] ),
            FILTER (
                ALLEXCEPT ( 'Sales(Product Level)', 'Sales(Product Level)'[AccountID] ),
                [ProductType] = "GPU"
            )
        ),
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( _RANK <= 5, _RANK )

Because there are only 7 account IDs in my sample data, I returned the top five account IDs.

 

I apologize again for not solving your problem in time.

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi Anonymous ,

 

After my test, creating a measure to get the top 1 product name does not work.

I finally achieved it by creating two tables. Because it is written in dax, if your data is updated, the result will also be updated.

TOP5ACCOUNT ID =
FILTER (
    SUMMARIZE (
        FILTER ( 'Sales(Product Level)', [ProductType] = "GPU" ),
        [ProductName],
        [Quantity],
        "top5",
            RANKX (
                FILTER ( ALL ( 'Sales(Product Level)' ), [ProductType] = "GPU" ),
                CALCULATE (
                    SUM ( 'Sales(Product Level)'[Quantity] ),
                    FILTER (
                        ALLEXCEPT ( 'Sales(Product Level)', 'Sales(Product Level)'[AccountID] ),
                        [ProductType] = "GPU"
                    )
                ),
                ,
                DESC,
                DENSE
            )
    ),
    [top5] <= 5
)

TOP 1 ProductName =
FILTER (
    SUMMARIZE (
        'TOP5ACCOUNT ID',
        [ProductName],
        [Quantity],
        "top1",
            RANKX (
                ALL ( 'TOP5ACCOUNT ID' ),
                CALCULATE (
                    SUM ( 'TOP5ACCOUNT ID'[Quantity] ),
                    ALLEXCEPT ( 'TOP5ACCOUNT ID', 'TOP5ACCOUNT ID'[ProductName] )
                ),
                ,
                DESC,
                DENSE
            )
    ),
    [top1] = 1
)

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.