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 cannot upload the original file for personal reasons but I've included a Dummy pbix.file with the same structure, tables and relathionships which may helps us to find the solution.

 

Note1: "Family" has been picked up from Master_Item_All table to have less problem with aggreations created with other fact tables; "Budget" and "Open_Sales".

 

Note 2: These aggregations and some additional visuals have been created to evaulate how the entire table and measures behave in context.

 

https://app.powerbi.com/view?r=eyJrIjoiMGU3NGQ1YTMtNTJkMi00NDNjLTg1ZmMtYmY2ZTBiYWVhYjBiIiwidCI6IjBkNmJmMDhmLTI3YTgtNDczOS04YWQ1LWQ3YTZkNzRjY2ZjYSIsImMiOjh9

 

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