Column flag for top percentage

[nikos_derv]

Hello,

I need to create a calculated column where it crates a flag (yes/no) for the top 10% per category. Any saggestions? 

To be more precise, i need to flag in a customer per store dataset, the top 10% customers per store, based on their spending.

Many thanks in advance!

[danextian]

Hi @nikos_derv ,

This can by creating several calculated  columns but a warning, this looping in each row per category and can be very slow on  large table. I'd like to see a more efficient to this approach if there is.

Percentage = 
//value percentage within a category
VAR CategoryTotal =
    CALCULATE (
        SUM ( 'DataTable'[Values] ),
        ALLEXCEPT ( 'DataTable', 'DataTable'[Category] )
    )
RETURN
    DIVIDE ( 'DataTable'[Values], CategoryTotal )

Percentage Rank within a category = 
//rank the percentage with highest as one
RANKX (
    FILTER (
        'DataTable',
        'DataTable'[Category] = EARLIER ( 'DataTable'[Category] )
    ),
    'DataTable'[Values],
    ,
    DESC,
    DENSE
)

The formula below can be very slow - might cause memory crash/webview2 error/bluescreen error.

Top10 =
//calculates the runninng percentage based on the rank and by category
//returns true if the running percentage is <= 10%
CALCULATE (
    SUM ( 'DataTable'[Percentage] ),
    FILTER (
        FILTER (
            'DataTable',
            'DataTable'[Category] = EARLIER ( 'DataTable'[Category] )
        ),
        'DataTable'[Percentage Rank within a category]
            <= EARLIER ( 'DataTable'[Percentage Rank within a category] )
    )
) <= .10

Please refer to the attached pbix. Go to the dataview then select Datatable table

[nikos_derv]

Thank you @danextian for your reply!

I didn't get to use exactly your suggestion but you definately showed me the right direction.

In case anyone else might have the same issue, my solution was: 

-create a column to calculate rank per category, using RankX (as mentioned above)

-create a column to calculate the max value of rank column, for each category 

-final flag column: IF('rank column' <0.1 * 'max rank per category column' ,1,0)

Again, many thanks for our support!