How to display the most frequent item from another table in PowerBI?

Question

I have 2 tables:

ProductSales: each row represents a sold product (ex: ball, chair, glass ...)
Regions: each row represents a geographical region (ex: Europe, America ...) where a product can be sold

These 2 tables are connected to one another by a one to many relationship (1 Region -> * Products) through a column called RegionId which indicates where each product in the table ProductSales was sold.

My goal is to display in the Regions table the name of the most sold product which can be calculated from the ProductSales for each region. Would you be able to provide a smart and elegant way to write the correct DAX formula to achieve this task?

I think I need to use the function RELATEDTABLE() function and combine it with some additional function but I had no success thus far. This is what I tried so far:

GROUPBY('ProductSales', 'ProductSales'[RegionId], "Most Frequent Item", 
FIRSTNONBLANK (
    TOPN (
        1, 
        VALUES(ProductSales[ProductKey]), 
        RANKX( ALL( 'ProductSales'[ProductKey]), COUNTROWS(CURRENTGROUP()),,ASC)
    ), 
    1 
))

Kin Siang · Answer 1 · 2021-06-13T02:20:29.743

0

You may not need any dax to display Top sales for each region, unless there is other restriction that I am unaware:

Suppose this is your original data with 3 type of products shoe, bag and shirt being sold in China, US and Euro using region ID:

This will be table report without any filter by first create relationship for the Region ID to display as country:

By using Top N filter in the visualisation, it will be clear that shoe is the best sale for each region:

edited Jun 13 '21 at 02:20

answered Jun 13 '21 at 02:11

Kin Siang

2,644
2
4
8

Thank you for your input :) However this is not what I am trying to achieve since I am trying to add a calculated column to the Regions table. However leave your answer anyways because it could be helpful for somebody else! – Federico Gentile Jun 13 '21 at 06:39
Ya, I saw your answer already, it is good also :) – Kin Siang Jun 13 '21 at 08:00

score 0 · Accepted Answer · answered Jun 13 '21 at 06:49

I was able to achieve my goal in 2 steps:

Step 1

Add a column in the table ProductSales which displays the most frequent item in column ProductKey for each RegionID:

Top Item = 
VAR CurrRowTerritory = 'ProductSales'[RegionId]
VAR ProductKey =
    SUMMARIZE (
        FILTER ( 'ProductSales', 'ProductSales'[RegionId] = CurrRowTerritory ),
        'ProductSales'[ProductKey],
        "ProdCount", COUNT ( 'ProductSales'[ProductKey] )
    )
RETURN
    MAXX(TOPN ( 1, ProductKey, [ProdCount] ),  'ProductSales'[ProductKey])

Step 2

Use the RELATEDTABLE() function to import on Regions the information just created in ProductSales

TopItem = MAXX(RELATEDTABLE(FactInternetSales), 'FactInternetSales'[Top Item])

Note: my answer was inspired by this question.

How to display the most frequent item from another table in PowerBI?

2 Answers2