Display records for Top category and Others

[Sharmi_28]

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

[talespin · Accepted solution]

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() )

    )

)

[Sharmi_28]

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?

[talespin]

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 )

)

[Sharmi_28]

@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?

[talespin]

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")

[Sharmi_28]

@talespin 
Thank you so much. 🙂

[talespin]

Hi @Sharmi_28 

You're welcome.

[talespin]

hi @Sharmi_28 ,

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

[lbendlin]

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