Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
artur4
Helper I
Helper I

TopN - Rows increase when adding a column

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.

artur4_0-1714560865137.png

 

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:

CustomerCat 1Cat 2Cat 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? 

1 ACCEPTED SOLUTION
artur4
Helper I
Helper I

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)

View solution in original post

2 REPLIES 2
artur4
Helper I
Helper I

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
Community Support
Community Support

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.