topic Re: TopN + Others - getting required output but table visual total is Wrong in DAX Commands and Tips

TopN + Others - getting required output but table visual total is Wrong

Anonymous — Wed, 24 Jan 2024 11:09:00 GMT

Dear ,

Created measure for TopN + Others with help of community post thred , but I'm facing the issue with Total.

Here is the steps

1) Created a table :

Segment Table =

UNION (DISTINCT(

ALLNOBLANKROW ( Product_Master[Seg] )),

{ "OTHERS" }

)

2) Created Rank Measure :
Seg_Rank =
RANKX(ALLSELECTED('Segment Table'[Seg]),[Value])

3) Created Topn+ Others measure

TopN+Others =
VAR __TopN =
TOPN ( [TopN Value], ALL ('Segment Table'), [Value], DESC )
RETURN
IF ( [Seg_Rank] <= [TopN Value],[Value],
IF ( SELECTEDVALUE ( 'Segment Table'[Seg] ) = "OTHERS",
CALCULATE ([Value], ALL ( 'Segment Table'[Seg] ) )- SUMX ( __TopN, [Value] ),
BLANK ()
)
)

From these getting expected result for Top N and Others but the total is the sum of TopN values not included "Others" so I created one more measure (found some soln in Community)

Topn + Other = SUMX(SUMMARIZE('Segment Table','Segment Table'[Seg]),[TopN+Others])

But now the total is correct but value for "Others" is blank

Can any one suggest any solution for this?

Thanks in advance.

Re: TopN + Others - getting required output but table visual total is Wrong

Anonymous — Wed, 24 Jan 2024 13:07:39 GMT

Understood , this because of filter applied in the table visual as I need to sort the visual by "Segment Rank" which should not shown in the table. so created filter TopN_Filt and applied .

Any solution to fix this or how to sort a table (in chart we can add any sort column to Tooltip) with another measure?

Re: TopN + Others - getting required output but table visual total is Wrong

Anonymous — Thu, 25 Jan 2024 09:05:52 GMT

Hi, Anonymous

Based on the data provided in your table, I gave a simple example to illustrate my understanding.I created a simple table with Culumn2 is sorting broken up.

If you need to sort it, you can New Column in Power Query and then sort the New Column.

So you can sort the data in the table. This is only my partial answer to your question, if it doesn't solve your doubts, provide me with more data or more details.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yilong Zhou

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

Re: TopN + Others - getting required output but table visual total is Wrong

Irshadn24 — Thu, 25 Jan 2024 09:22:02 GMT

Thank you dear Anonymous for your reply
My requirement is to sort the table visual by a measure "Seg_Rank" (which Dynamically change based on the slicer selection) and no need to show the "Seg_Rank" in Table visual.
Will add link for sample file