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

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi Ciria,

 

Can you share the pbix file? 

Where is the Family field in the visual from? Try to add the index in the table where the Family is from and try this formula please:

Total Sales Acumulative 2 =
CALCULATE (
    [Total Sales LY];
    FILTER ( ALLSELECTED ( 'TableOfFamily' ); 'TableOfFamily'[Index] <= MAX ( 'TableOfFamily'[Index] ) )
)

Best Regards,

Dale

Ciria's avatar
Ciria
Advocate III
8 years ago

Hi v-jiascu-msft

 

I cannnot upload the file for personal reasons, but I've created a dummy pbix.file for your perusal.

 

This file contains the same table structure, relathionships, and calculations than the original.

 

Note 1: I've used "Family" from "Master_Item_All" (Dimensional) to avoid problems with the cross-calculations done with the others two fact tables; "Budget" and "Open_Sales".

Some aggregations and additional visuals have been added to check the overall performance when drilling.

 

Note 2: I hav uploaded this file to Dropbox for downloading, let me know if it doesn't work.

 

https://www.dropbox.com/s/6mjtob5mo7x9gw1/Dummy%20Model.pbix?dl=0

 

Awaiting for your answer.:smileyvery-happy: