Ranking and filter based on rank - a tricky problem

[anandav]

Hi,

I have a simple data model to test RFM classification (marketing).

See the PBIX file here.

Using the visuals I can answer the question Which customer segment drove sales and which products in what category contributed to that sales.

But the requirement is to construct the answer in words.

The top classification contributing to sales is 'New' customers ($323). These customers mostly bought products from category 'Cat1' ($237) which consists of 'P2' ($132) and 'P1' ($105).

I have dome similar sentence constrcution using measures but in this I am unable to get some basic measures working.

Help needed:

1. I wanted to rank the sales based on:

   - Calssification

   - Classification by Category

   - Classification by category by product

Then I could use the ranks to filter the top N items as required. e.g. top classification, top category, top 2 products

But I couldn't get even the first RANKX working.

mRFM_Cat_RANK =

RANKX (

       FILTER (

        ALL(RFM[cstRFMclassification]),

        RFM[cstRFMclassification] = MAX(RFM[cstRFMclassification])

    ),

    CALCULATE(SUM(RFM[cstPurchase$])),

)

2. How can I get the top N to construct the sentence?

Any help will be greatly appreciated.

I am open to any changes in the data model or any other aspects of the pbix file.

Thanks in advance.

[v-shex-msft]

Hi @anandav ,

Do you mean to create dynamic calculate columns base on filters? AFAIk, current power bi does not support this, filter and slicer are work on data view level(visual level) which generated from data model tables. Their filter effect does not really affect table records. (In my opinion, I'd like to suggest you use measure instead, they can interact with filer/slicers)

In addition, I found you link your table with the single-direction relationship, it only works from one side. You can refer to the following blog to know more about this:

What is the Direction of Relationship in Power BI? 
Regards,

Xiaoxin Sheng

[anandav]

Actually I got  the measures working to do the rank.

What I would like to get help on is how can I construct the insight from visual into words as shown in the example.