TopN - Rows increase when adding a column

[artur4]

Hello, 

I am trying to calculate the TOP 5 Customers (by considering the Tot Sales) which have negative delta vs profit average. This is working with the following measure:

TOP 5 Customers =

 VAR tab= ADDCOLUMNS(SUMMARIZE(FILTER(ALLSELECTED('DATA'[Customer Name]), [GM% vs Country AVG]<0), 'DATA'[Customer Name]), "delta GM", [GM% vs Country AVG])

VAR C1 = VALUES ('DATA'[Customer Name])

RETURN

            CALCULATE(

                IF(NOT ISBLANK([GM%]), [GM%] - CALCULATE([GM%], ALLEXCEPT('DATA', 'DATA'[Fiscal Year], 'DATA'[Scenario], 'DATA'[Country], 'DATA'[Category]))),

                TOPN( 5, tab, [NOS M (USD)], DESC),

                C1

            )

As you can see above, I need to keep the filtering on Year, Category, Scenario and Country.

Also, I need to see the TOP 5 Customers which have negative delta vs profit average and the related categories. However, when I use the same measure and I add the Categories field in my matrix, Power Bi is showing me the TOP 5 Customers (based on Sales) for each category.

I want to have a result like this:

Customer	Cat 1	Cat 2	Cat 3
Alpha	-1%	-1%	-5%
Beta	-2%	-4%	-1%
Gamma	-1%	-2%	-1%
Delta	-3%	-3%	-1%
Teta	-2%	-2%	-1%

Do you have any ideas on how to display only TOP 5 Customers and show the Categories in the columns? 

[artur4 · Accepted solution]

Thank you for your reply.

Unfourtunatly it does not work. I cannot add a column from another table with ADDCOLUMNS, I need to have a measure for the new column.

I fixed the issue by creating a new calculated table which is taking only the relevant columns for the calculations and grouping. Then, I created some measures for the calcualtions. 

It's not an efficiendt way but it works (I was hoping to do everything in one measure by leveraging the VARIABLES)

[v-jiewu-msft]

Hi @artur4 ,
Based on the description, please try the following dax formula.

Top5CustomersCategories =
VAR CustomerTable =
    ADDCOLUMNS(
        SUMMARIZE(
            FILTER(ALLSELECTED('DATA'[Customer Name]), [GM% vs Country AVG] < 0),
            'DATA'[Customer Name],
            "DeltaGM", [DeltaGM]
        ),
        "Category", 'DATA'[Category]
    )
RETURN
    TOPN(5, CustomerTable, [DeltaGM], DESC)

If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format to make a deep troubleshooting? It would be helpful to find out the solution.

How to provide sample data in the Power BI Forum

Best Regards,

Wisodm Wu

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