Pie chart with other category

[Umidjon]

Hi there, 

I want to get only top N categories based  on their values and create other category. I used rankx dax formula (created column). However, I could not get expected result. What I have to do in order get expected result?

Thanks in advance

[Jihwan_Kim · Accepted solution]

hi, @Umidjon 

I am not sure if I understood your question correctly, but please check the below picture and the sample pbix file.

I tried to create a sample pbix file based on the explanation.

I suggest creating a dim-category table like below.

Qty Total =

SUM(Sales[Qty])

Show Top N =
VAR _topNshow =
SUMX (
KEEPFILTERS (
TOPN (
SELECTEDVALUE ( 'TopN'[TopN] ),
ALLSELECTED ( Categories ),
[Qty Total], DESC
)
),
[Qty Total]
)
RETURN
SWITCH (
TRUE (),
SELECTEDVALUE ( Categories[Category] ) = "Others",
CALCULATE ( [Qty Total], ALLSELECTED ( Categories ) )
- SUMX (
TOPN (
SELECTEDVALUE ( 'TopN'[TopN] ),
ALLSELECTED ( Categories ),
[Qty Total], DESC
),
[Qty Total]
),
_topNshow
)

https://www.dropbox.com/s/utb8w0mfkd8i96w/umidjon.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[kg4u]

Hi,

I am trying to use your solution to show the TopN and the Others in a Pie Chart.  The TopN is working perfectly, but I am not able to get the "others" to display in the chart.  The chart is only showing the TopN.  How can I resolve this?  

Here's the calculation I am using for TopN

Show Top N =

VAR _topNshow =

SUMX (

KEEPFILTERS (

TOPN (

SELECTEDVALUE ( 'TopN'[TopN] ),

ALLSELECTED ( 'Product Totals'[Product Name] ),

[Qty Total], DESC

)

),

[Qty Total]

)

RETURN

SWITCH (

TRUE (),

SELECTEDVALUE ( 'Product Totals'[Product Name]) = "Others",

CALCULATE ( [Qty Total], ALLSELECTED ( 'Product Totals'[Product Name] ) )

- SUMX (

TOPN (

SELECTEDVALUE ( 'TopN'[TopN] ),

ALLSELECTED ( 'Product Totals'[Product Name] ),

[Qty Total], DESC

),

[Qty Total]

),

_topNshow

)