Forum Discussion

Ciria's avatar
Ciria
Advocate III
8 years ago
Solved

Cumulative Sales Without Dates on Rows

Dears :smileysad:   These days I am working in building a table with manifolds KPI's to monitor sales evolution. Following Commercial Chief Officer's advices I'm creating two tables, one table lead...
Need Help
Tips and Tricks
Tutorial Requests
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Ciria,

     

    I created a solution based on your sample.

    1. Add an index column in the table "Master_Item_all".

    Index = mid([Family], 8, 3)  // why 3? In case you have "Family 100"

    Cumulative_Sales_Without_Dates_on_Rows

    2. Create a new measure.

    Total Sales Cumulative =
CALCULATE (
    [Total Sales],
    FILTER (
        ALL ( Master_Item_All ),
        Master_Item_All[Index] <= MAX ( Master_Item_All[Index] )
    )
)

    Cumulative_Sales_Without_Dates_on_Rows2

     

     

    Best Regards,

    Dale

Ciria's avatar
Ciria
Advocate III
8 years ago

Hi v-jiascu-msft

 

In the report uploaded (dummy), Families appear like Family 1,  Family 2 and so on so forth.

In the private model I am using families are like: Milk, Meal, Fish Oil, Butter, etc, so, different caracters lenght or not folllowing a continuos List as on Dummy model.

 

Within Dummy Model I've created two measures.

  1. Using your idea with Index, creating a Measure to create cumulative sales when you order by Family name.
  2. Using Rankx function and total sales, creating a Measure to create cumulative sales based on Rank or Total Sales.
  3. See Report 1 Page to check it out.

 

Awaiting for your feedback :-)

 

Regards,