How to calculate Fixed set of Top customers and represent data in Customer and Date level

[SravaniG]

Hi,

I really need help on the below issue, 

I have two different date sclicers in my report, by using first date slicer range i am calculating revenue and generating topn customers by revenue dynamically.

In small multiple graph, I am using customer column in small multiples,second date slicer (Last N months relative date range) column on Axis. My requirement is to show revenue for fixed set of top customers generated in the above step,but I am getting more customers than topN customers. (ex: i kept top 10, so i need to generate 10 trellis one for each customer but i am getting 13 trellis)

 Calculations i am using:

created disconnected date table as Calendar'[Current Date]

Revenue=CALCULATE([Revenue],FILTER('Table'[Date]>=MIN('Calendar'[Current Date]) && 'Table'[Date]<=MAX('Calendar'[Current Date]))

Rank=RANKX(ALLSELECTED('Table'[Customer]),[Revenue],,DESC)

Top N =

Var Topvalue = SELECTEDVALUE('Top'[Top])

return

IF([Rank]<= Topvalue,1,0)

Kept TopN=1 as filter in filters section to get topN customers dynamically

[v-zhenbw-msft]

Hi @SravaniG ,

Your idea is correct, we have also tested it, this method is feasible.

The possible problem is that your customer revenue has duplicate values, so when you select Top 10, there are 13 customers.

You need to completely separate revenue to get the precise top ten.

You can add an index column, and change the revenue measure like this,

Revenue =
CALCULATE (
    SUM ( 'Table'[value] ),
    FILTER (
        'Table',
        'Table'[Date] >= MIN ( 'date table'[Date] )
            && 'Table'[Date] <= MAX ( 'date table'[Date] )
    )
)
    + CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            'Table',
            'Table'[Date] >= MIN ( 'date table'[Date] )
                && 'Table'[Date] <= MAX ( 'date table'[Date] )
        )
    ) * 0.00001

Then rank the revenue measure.

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.