Cumulative Sales by Rank

[HR3038511]

Hi,

I have a sales table with sales per sales agent. The table has a rank by sales. I need to calculate cumulative sales for the subsequent 3 ranks for each sales agent. Filters for category and sale agent should still be working on the result.

My sales table:

The result I need I look like the table below. For each rank the sum of the previous 2 ranks should be cumulated. Filter for category and sales agent should still work:

Here is the example file: https://easyupload.io/ukkg4n

Anybody any ideas?

Thanks!

[v-prasare]

@HR3038511, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[v-prasare]

@HR3038511, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[v-prasare]

@HR3038511, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[danextian]

Hi @HR3038511 

Please try this measure:

Subsequent 3 ranks = 
CALCULATE (
    SUM ( factSales[Sales] ),
    FILTER (
        ALL ( factSales[Rank] ),
        factSales[Rank] >= MAX ( factSales[Rank] )
            && factSales[Rank]
                <= MAX ( factSales[Rank] ) + 2
    )
)

[bhanu_gautam]

@HR3038511 

You can create a calculated column in your sales table to calculate the cumulative sales for the subsequent 3 ranks. 

DAX
Cumulative Sales =
VAR CurrentRank = Sales[Rank]
VAR CurrentCategory = Sales[Category]
RETURN
CALCULATE(
SUM(Sales[Sales]),
FILTER(
Sales,
Sales[Category] = CurrentCategory &&
Sales[Rank] >= CurrentRank &&
Sales[Rank] < CurrentRank + 3
)
)

Ensure that your filters for category and sales agent are applied to the visualizations where you use the cumulative sales column. Power BI will automatically respect these filters when calculating the cumulative sales.

Add a table visualization to your Power BI report and include the Rank, Category, Sales Agent, Sales, and the new Cumulative Sales column.

[HR3038511]

@bhanu_gautam The context of the visual should be regarded. Meaning when I only have the rank column it should calculate the overall sum of sales for the last 3 ranks regardless of category and sales agent:

Any further ideas?