Forum Discussion

HR3038511's avatar
HR3038511
Icon for Helper I rankHelper I
1 year ago

Cumulative Sales by Rank

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!

 

Need Help

6 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      HR3038511
      Icon for Helper I rankHelper I
      1 year ago

      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? 

       



  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    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
    )
)

     

  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    1 year ago

    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's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    1 year ago

    @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's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    1 year ago

    @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