topic Re: Display records for Top category and Others in DAX Commands and Tips

Display records for Top category and Others

Sharmi_28 — Mon, 05 Feb 2024 12:01:49 GMT

Please help me on following scenario
I have FactMain table with column ProductCategory , CustomerName, PurchasedDate.
1. how to get list of Top 20 CustomerName who purchased more product based on ProductCategory.
And all other products (not in Top 20) will be consider as "Others" (ProductCategory)
For Example

Table Visual 2 should be show the list, when I click on particular ProductCategory of Table visual 1
I am able to show the list for all Product Category except "Others"
When I clicked on Others from table visual 1 , Table visual showing blank
Please help

Re: Display records for Top category and Others

lbendlin — Mon, 05 Feb 2024 22:51:01 GMT

https://www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/

Re: Display records for Top category and Others

talespin — Tue, 06 Feb 2024 07:23:20 GMT

hi Sharmi_28 ,

Tried to create Table Visual1 using Adventureworks SQL database. Please replace table name and column names with yours.

STEP1

Create a calculated table and do not link it to anything. Use product from this table for the visuals.

Products =

UNION(

SELECTCOLUMNS( VALUES(DimProduct[EnglishProductName]), "ProductName", [EnglishProductName]),

{"Others"}

)

STEP2

Create this measure

MTopCustomersByProduct =

VAR _TopN = 20

VAR _ProductName = SELECTEDVALUE(Products[ProductName])

VAR _AddCount =

ADDCOLUMNS(

VALUES(DimProduct[EnglishProductName]) ,

"@CountProductsByCustomer", CALCULATE( COUNT(FactInternetSales[CustomerKey]))

)

VAR _AddRank =

ADDCOLUMNS( _AddCount,

"@RankProductByCustomer", RANK( DENSE, _AddCount, ORDERBY([@CountProductsByCustomer], DESC))

)

RETURN

IF( _ProductName = "Others",

SUMX(

FILTER(_AddRank, [@RankProductByCustomer] > 20),

[@CountProductsByCustomer]

MINX(

FILTER(_AddRank, [EnglishProductName] = _ProductName),

IF( [@RankProductByCustomer] <=20 , [@CountProductsByCustomer], BLANK() )

)

Re: Display records for Top category and Others

Sharmi_28 — Tue, 06 Feb 2024 08:35:22 GMT

Hi talespin
Really appriciate for your solution.
With your suggested measure MTopCustomersByProduct we got the Customer Count By Product.
I Also want customer details in other table visual
for example , when I click on Product Name "water bottle - 30 oz" from table visual the all customer who purchased "water bottle - 30 oz" list should be display to other table visual.

This is working fine for all products but not work for "Others"
How to get customer details who purchased those product which contains in Others?

Re: Display records for Top category and Others

talespin — Tue, 06 Feb 2024 08:40:28 GMT

hi Sharmi_28 ,

In your detail table visual, add only columns from your linked data model(not the calculated table).

Create a measure like this and apply it as a filter on your detail table visual.

_SelProdctName - This is product from Calculated table, the table on left side.

_SelDimProductName - This is product from Data model on teh right side.

Tested it for last record and I was seeing difference of 2. Instead of 258 i got 256 when exported visual to csv. Please test it thoroughly.

Measure

-------------------------------

IsSelectedProduct =

VAR _SelProdctName = SELECTEDVALUE(Products[ProductName])

VAR _SelDimProductName = SELECTEDVALUE(DimProduct[EnglishProductName])

VAR _TopN = 20

VAR _ProductName = SELECTEDVALUE(Products[ProductName])

VAR _AddCount =

ADDCOLUMNS(

VALUES(DimProduct[EnglishProductName]) ,

"@CountProductsByCustomer", CALCULATE( COUNT(FactInternetSales[CustomerKey]))

)

VAR _AddRank =

ADDCOLUMNS( _AddCount,

"@RankProductByCustomer", RANK( DENSE, _AddCount, ORDERBY([@CountProductsByCustomer], DESC))

)

VAR _FilterTable = FILTER( _AddRank, [@RankProductByCustomer] <= 20)

VAR _CountProduct =

COUNTX( FILTER(_FilterTable, [EnglishProductName] = _SelDimProductName), [EnglishProductName])

RETURN IF( _SelProdctName = "Others",

IF(_CountProduct = 0, 1, 0),

IF(_SelProdctName = _SelDimProductName, 1 , 0 )

)

Re: Display records for Top category and Others

Sharmi_28 — Tue, 06 Feb 2024 09:35:25 GMT

talespin Thanks a lot.
I applied measure as filter suggested by you.
but its still display blank table (right side) when I click on Others product from left side table.

What happen when you click on Others product?

Re: Display records for Top category and Others

talespin — Tue, 06 Feb 2024 09:58:01 GMT

hi Sharmi_28 ,

Have to think of some other approach for Visual2, will let you know either way.

Re: Display records for Top category and Others

talespin — Tue, 06 Feb 2024 13:49:03 GMT

hi Sharmi_28 ,

Sorry I don't have a perfect solution, its a work around.

Problem - I can't run any measure on customer detail table as it will fail as it needs to be eveluated for every row of customer, but I can run it on a Product table. Using Intermediate table. With below measure when "Others" is selected from first table visual, it will remove all products which have a rank <= 20 and keep everything else. Then use this table to show customers for one selected product. Place a filter(IsSelectedProduct = 1) on second table visual as shown in second screenshot.

IsSelectedProduct =

VAR _SelProdctName = SELECTEDVALUE(Products[ProductName])

VAR _SelDimProductName = SELECTEDVALUE(DimProduct[EnglishProductName])

VAR _AddCount =

ADDCOLUMNS(

FILTER( ALL(DimProduct[EnglishProductName]), TRUE() ),

"@CountProductsByCustomer",

VAR _PName = [EnglishProductName]

RETURN CALCULATE( COUNT(FactInternetSales[CustomerKey]), REMOVEFILTERS(DimProduct), DimProduct[EnglishProductName] = _PName)

)

VAR _AddRank =

ADDCOLUMNS( _AddCount,

"@RankProductByCustomer", RANK( DENSE, _AddCount, ORDERBY([@CountProductsByCustomer], DESC))

)

VAR _RANK = MINX( FILTER(_AddRank, [EnglishProductName] = _SelDimProductName ), [@RankProductByCustomer] )

RETURN IF( _SelProdctName = "Others",

IF(_RANK > 20, 1, 0),

IF(_SelProdctName = _SelDimProductName, 1 , 0 )

)

Used this measure to hide the customer detail table, unless product is selected from DimProduct. Set it on shape visual Background color and overlay on customer table visual. You will need to use CTRL key to select values from first and second table, otherwise as soon as you select value from 2nd table, it will vanish as onclick will clear selection from first table.

BGColor =

IF( ISBLANK(SELECTEDVALUE(DimProduct[EnglishProductName])), "#FFFFFF", "#FFFFFF00")

Re: Display records for Top category and Others

Sharmi_28 — Wed, 07 Feb 2024 06:03:50 GMT

talespin
Thank you so much. 🙂

Re: Display records for Top category and Others

talespin — Wed, 07 Feb 2024 07:26:42 GMT

Hi Sharmi_28

You're welcome.