TOPN + Others not aggregating properly with Legends

[meeen-ware]

Hello dear Fabric Community! After many years of finding solutions here, it is my time to ask for help 🙂

I am working in a TOPN + Others problem, that is making my head spin. I am creating  3 different measures like this:

X Measure= SUMX(

        SUMMARIZE(

            'Original Table', 'Attribute 1 Table'[ID], 'Attribute 2 Table'[ID], "@X", [X]), [@X]).

I am calculating X as

X =

CALCULATE(

    [Qty],

    NOT ('Original Table'[Category_1] IN { "Value 1", "Value 2" }),

    'Original Table'[Category_2] = "ANOTHER_VALUE"

)

I had to create them like this as my totals were not aggregating properly due to some overall quantities being positive, but when used with certain attributes for legends, the sum included some negatives. Anyway, that part seems to be working fine! I also created a Total Qty = X + Y + Z. My model is normalized, so I have a central fact table with lots of values, and a lot of additional dimension tables with details for some attributes.

However, when aggregating and due to Power BI limitations on the 60 legends limit, when plotting some of this in a column chart it would not display accurate values. I arrived at the conclusion that I needed to use TOPN + Others for this charts, and possibly limit TOPN to have an overall number of legends on screen less than 60. 20 seems to be a sweet spot. Now comes the challenge!

I watched some tutorials and arrived to the conclusion that a measure like this would do the trick:

TOPN_ =

VAR IDTopN = SELECTEDVALUE('TOP N'[TOP N])

VAR IsOtherSelected =

    SELECTEDVALUE ( 'Attribute 2 Table'[ID] ) = "Other"

VAR tab =

CALCULATETABLE (

    VALUES ( 'Attribute 2 Table'[ID] ),

    ALLSELECTED ( 'Attribute 2 Table'[ID])

)

VAR _Rank =

IF(

    ISINSCOPE ('Attribute 2 Table'[ID]),

    RANKX (

        tab,

        [Total Qty]

    )

)

VAR TopNValues =

SUMX (

    TOPN (

        IDTopN,

        ADDCOLUMNS (

            tab,

            "@SumValue",[Total Qty]

        ),

        [@SumValue]

    ),

    [@SumValue]

)

VAR OtherValues = SUMX ( ALLSELECTED ( 'Attribute 2 Table'[ID] ), [Total Qty]) - TopNValues

RETURN

IF (

    IsOtherSelected,

    OtherValues,

    IF (

        _Rank <= IDTopN,

        [Total Qty]

    )

)

Now, with the help of another table with TOPN values I can see my chart and it produces almost the same as [Total Qty]. There is a small difference, that sometimes I have been able to identify as this.

If I do a table with Attribute 2 and my Total Qty, X, Y and Z values it looks like this (I have around 20k IDs for this attrbute):

	Total Qty	X	Y	Z
ID 1
ID 2
ID 3
ID 4
ID 5
ID 6
...	...	...	...	...
TOTAL	1 000 000	500 000	200 000	300 000

Imagine some values there that end up adding to the respective quantities. However, if I add my TOPN Qty, I get a slightly less qty, maybe something like 998 400. So I've been looking at the data and noticed that some of the "Attribute 2" ID's have negative values. I Filtered my table to display only Total Qty < 0 and see something like this:

	Total Qty	X	Y	Z
ID X	- 900	- 500	- 100	- 300
ID Y	- 700	- 400	- 200	- 100
Totals	-1600	- 900	- 300	-400

And those are exactly my missing QTY from TOPN. So I have the following questions.

• Am I doing something wrong during the calculations that is somehow excluding the values for these negatives while calculating my measures, X, Y and Z?
• Am I doing something wrong during the calculations that is removing the conditions on X, Y and Z when doing the Total Qty or the subsequent TOPN + Others?

Additional to this, I noticed that if my TOPN value is 5, and if I do some other aggregation on the column chart like, month or year, most values look OK (X, Y, Z are time dependent) and there's always an error with current month/year/Qtr (which is the one above). However, If I change my TOPN to 10, 15, or 20, I will also see some additional months/qtrs/years start to have issues. I was thinking this was due to the additional number of labels and for this I have not been able to identify any data points that might cause it, since even selecting 25 as my value does not increase my total number of legends to above 60.

If additional details are needed, let me know. This has been giving me headaches for a bit now.

Thank you!

[Anonymous]

Hi @meeen-ware ,

In order to get a better understanding on your problem and make further investigation, could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

In addition, you can refer the following links to get TOP N +Others...

Dynamic TOP N and Others in Power BI - YouTube

Filtering the top products alongside the other products in Power BI - SQLBI

Dynamic and nested Top N + Others in Power BI | by Inforiver | Medium

Dynamic and nested Top N + Others in Power BI - Inforiver

Best Regards

[meeen-ware · Accepted solution]

Ended up being two things:

a) Aggregations at the Category level were different than at the top level. Imagine if sales by Country are positive, but when using states, there are a couple negative. Changed measures to review this.

b) Dataset already contained an "Other" value with values, so when using my formula it was complewtelky skipping "Other". Renamed to "Others" and problem is now I have "Other" and "Others" but this is due to dataset "errors".

Thanks! It took quite a few days to fix.

[Anonymous]

Hi @meeen-ware ,

It's glad to hear that you solved the problem. And thanks for sharing your solution here, it will be very helpful if the others in the community face the similar problem as yours. Could you please mark your post as Answered since it has been resolved? Thank you.

Best Regards

[meeen-ware]

Thanks, I have used those as reference. Let me try to mask some data so it can be provided through a PBIX.