topic Re: Group a couple of items in "Others" in DAX Commands and Tips

Group a couple of items in "Others"

Anonymous — Thu, 03 Mar 2022 09:57:18 GMT

Hi all

I have a list of sold items (for example 10)

I build the sales measure and now I want to show on a chart only s specific 3 items and all the rest should be sum together and called "Others"

How could I do that using only DAX, the model prevents me from adding any columns, so everything should be done with DAX.

Thanks in advance

Regards

Re: Group a couple of items in "Others"

amitchandak — Thu, 03 Mar 2022 12:07:07 GMT

Anonymous , You need to create a new column or group

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning

Re: Group a couple of items in "Others"

Anonymous — Thu, 03 Mar 2022 12:15:52 GMT

Dear Sir

Thank you for the fast reply, and this is a great solution 😎

Unfortunately, I don't have access to the Data tab within the model so I cant group the columns within tables 😞

I hope you could help is solving this by using only DAX

Thanks in advance

Regards

Re: Group a couple of items in "Others"

tamerj1 — Thu, 03 Mar 2022 13:49:32 GMT

Anonymous
Can you create new table?

Re: Group a couple of items in "Others"

Anonymous — Thu, 03 Mar 2022 14:16:14 GMT

Unfortunately, no , the model is so protected, you can only add measures

Re: Group a couple of items in "Others"

Whitewater100 — Thu, 03 Mar 2022 14:39:07 GMT

Hi:

This is a big one! There are a few steps, a couple of measures which I will paste below. You'd need to change out your table and column names.

Sales Amt =

VAR SalesOfAll =

CALCULATE (

[Sales Amount],

REMOVEFILTERS ( 'Product Names' )

)

RETURN

IF (

NOT ISINSCOPE ( 'Product Names'[Product Name] ),

-- Calculation for a group of products

SalesOfAll,

-- Calculation for one product name

VAR ProductsToRank = [TopN Value]

VAR SalesOfCurrentProduct = [Sales Amount]

VAR IsOtherSelected =

SELECTEDVALUE ( 'Product Names'[Product Name] ) = "Others"

RETURN

IF (

NOT IsOtherSelected,

-- Calculation for a regular product

SalesOfCurrentProduct,

-- Calculation for Others

VAR VisibleProducts =

CALCULATETABLE (

VALUES ( 'Product' ),

ALLSELECTED ( 'Product Names'[Product Name] )

)

VAR ProductsWithSales =

ADDCOLUMNS (

VisibleProducts,

"@SalesAmount", [Sales Amount]

)

VAR SalesOfTopProducts =

SUMX (

TOPN (

ProductsToRank,

ProductsWithSales,

[@SalesAmount]

)

VAR SalesOthers =

SalesOfAll - SalesOfTopProducts

RETURN

SalesOthers

)

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Ranking =

IF (

ISINSCOPE ( 'Product Names'[Product Name] ),

VAR ProductsToRank = [TopN Value]

VAR SalesAmount = [Sales Amount]

VAR IsOtherSelected =

SELECTEDVALUE ( 'Product Names'[Product Name] ) = "Others"

RETURN

IF (

IsOtherSelected,

-- Rank for Others

ProductsToRank + 1,

-- Rank for regular products

IF (

SalesAmount > 0,

VAR VisibleProducts =

CALCULATETABLE (

VALUES ( 'Product' ),

ALLSELECTED ( 'Product Names' )

)

VAR Ranking =

RANKX (

VisibleProducts,

[Sales Amount],

SalesAmount

)

RETURN

IF (

Ranking > 0 && Ranking <= ProductsToRank,

Ranking

)

I have a variable called ProductstoRank , you'll notice I have put in [Top N Value] , you can skip that and just put a number like 3 and it will group the others.

If you want to get fancy you can add a slicer that has how many items you want to see before the "others" are grouped. I learned this from sqlbi.

Hope it helps. Final output:

Re: Group a couple of items in "Others"

Anonymous — Mon, 07 Mar 2022 07:32:50 GMT

Hi Anonymous ,

Did you try to use "Group" as amitchandak mentioned?

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