Top N by categories

Question

I am working on the below calculation; I have tried different solutions but not able to get the required output. The sample table looks like below with ID, Amount, Category, Type;

I have a pie chart that shows the split of cash and card. Also, I created a measure to identify the average amount by each category like below.

AverageExpenseType = 
    AVERAGEX(
        SUMMARIZE('Data',
            'Data'[Category],
            "Total Spent", AVERAGE('Data'[Amount])),
        [Total Spent])

What I need to create is a table where the Top 5 ID's amount is more than the average to the respective Category and Type. i.e., when I select Cash in the pie chart, the average needs to calculated based on the cash or card as well.

For eg. the average of category A for cash is 4710 and the output looks like below where the ID having more than the average for Category A and Cash type

Can you add a sample output you wants? And please add some more sample data as you are looking for top 10 value. — mkRabbani
@mkRabbani Apologies for the delay. I have updated the sample data and output. — ssan

mkRabbani mkRabbani · Accepted Answer · 2020-08-12T10:43:22

you can create a custom column in your table with this below code-

category_type_wise_average = 

VAR current_category = pie_chart[category]
VAR current_type = pie_chart[type]

VAR category_wise_average = 
AVERAGEX(
    FILTER(
        pie_chart,
        pie_chart[category] = current_category
            && pie_chart[type] = current_type
    ),
    pie_chart[amount]
)

RETURN category_wise_average

Now you will have the Category and Type wise Average value in each row as shown in the below image. You can now apply your necessary filtering as per your requirements.

SOLUTION 2: Now, if you feel there is performance issue in generation a custom column with average calculation for every row, you can try this below option with same result-

Step-1: Create a new custom table with this below code-

pie_group_average = 

VAR sub_category_wise_total = 
GROUPBY (  
    pie_chart,
    pie_chart[category],
    pie_chart[type],
    "average",AVERAGEX(CURRENTGROUP(), pie_chart[amount])    
) 

RETURN
SELECTCOLUMNS (
    sub_category_wise_total,
    "category",pie_chart[category],
    "type",pie_chart[type],
    "average",[average] 
)

Step-2: Now create a Custom Column in your main table as below-

category_type_wise_average_2 = LOOKUPVALUE(
    pie_group_average[average],   
    pie_group_average[category],pie_chart[category],
    pie_group_average[type],pie_chart[type]
)

This measure will now hold the same value we generated in solution-1 with custom column.

Additional Steps:

Step-3: Please create another custom column as-

show_hide = 
IF(
    pie_chart[amount] >= pie_chart[category_type_wise_average_2], 
    1, 
    0
)

Step-4: Add these 2 visual level filter as shown in the below image-

Step by step change in data showing will be as below-

Hope this will help you!

Top N by categories

1 Answers