Most frequent products from three different columns combined together

[roger360]

I have a table TrackerData in PowerBI. Among several other columns,  there are three columns  D2D and Investment, Borrowing and Protection, and Cash Management. There are several various products listed under these three columns. I want to find the top most recurring products from all these three columns combined together. I want to create a card visual that will show the top most frequent product's name. FYI there are blank cells along with products under each columns. What should be the best approach to achieve this? 

[Anonymous]

Hi @roger360,

I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Please continue using Microsoft community forum.

Thank you,
Pavan.

[Anonymous]

Hi @roger360,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, kindly "Accept  as  Solution" and give it a 'Kudos' so others can find it easily.

Thank you,
Pavan.

[Anonymous]

Hi @roger360,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[rohit1991]

Hi @roger360 ,

The easiest way to find the most frequent product across three different columns is to unpivot those columns into a single column using Power Query. This stacks all your product names into one list, so you can count how often each one appears, no matter which original column it came from.

After unpivoting, just remove blanks, group by product, and count the number of times each appears. You can then sort the list or use a card visual with DAX to show the most common product. This method is much cleaner and makes it simple to analyze or update your logic later.

[roger360]

I tried doing that. The rows decreases from 20k to 15k. Not sure what went wrong. The steps I did:

1) Selected the three columns and did an Unpivot of Selected Columns.

2) Renamed newly created as Value column as Products.

3) Used the following formula to find calculate a measure to find the most frequent occurring products from the list which was used in a card visual

MostFrequentProduct =

CALCULATE(

    FIRSTNONBLANK(TrackerData[Products], 1),

    TOPN(

        1,

        SUMMARIZE(

            TrackerData,

            TrackerData[Products],

            "ProductCount", COUNT(TrackerData[Products])

        ),

        [ProductCount],

        DESC

    )

)

[Anonymous · Accepted solution]

Hi @roger360,

Thank you for reaching out in Microsoft Community Forum.

Thank you @danextian , @rohit1991, @Ashish_Mathur  for the helpful response.

Please use below DAX to resolve the error;

MostFrequentProduct =
VAR ProductTable =
SUMMARIZE(
FILTER(TrackerData, NOT(ISBLANK(TrackerData[Products]))),
TrackerData[Products],
"ProductCount", COUNTROWS(TrackerData)
)
RETURN
CALCULATE (
FIRSTNONBLANK(TrackerData[Products], 1),
TOPN(1, ProductTable, [ProductCount], DESC)
)

Please continue using Microsoft Community Forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan.

[danextian]

Hi @roger360 

Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

[roger3600]

I have attached the sample file link Data Sample . I want to create a card visual which will show the most frequent occuring products considering the three categories- Borrowing and Protection, D2D and Investment, and Cash Management. Based on the sample I am sharing, Credit Cards is the the most frequent product considering its occurence of 5 times across the three categories.

I want to create a card visual which will show that:

[Ashish_Mathur]

Hi,

Cannot download the file.  Access Denied message.

[roger360]

Apology @Ashish_Mathur 

it should be accessible now

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[roger3600]

Apologizing for the inconvenience. Should be accessible now. Thanks.