Forum Discussion
Cumulative Sales Without Dates on Rows
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"
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] ) ) )
Best Regards,
Dale
Hi Ciria,
How to accumulate sales by Family? For example:
Family Value
Family 1 100
Family 2 200
Family 3 300
Your expected result could be:
Family 1 Cumulative values
Family 1 100
Family 2 200 + 100 = 300
Family 3 300 + 200 + 100 = 600
Is this your desired result?
If so, maybe you can add an index in the Query Editor. Then filter the index like this.
Total Sales Acumulative 2 =
CALCULATE (
[Total Sales LY];
FILTER ( ALLSELECTED ( 'Sales' ); 'Sales'[Index] <= MAX ( 'Sales'[Index] ) )
)Best Regards,
Dale
Thanks for the comment and suggestion, it was definately a good ID, and yes, this is what I am looking for.
Unfortunately it doesn't work, I've tried adding a new Index Column in Sales Table, and creating a new measure in the way you commented.
It works in cumulative perspective, but it is not taking the sales values properly, values are huge!...
Awaiting for your support.
Regards,
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
I cannot upload the original file for personal reasons but I've created this dummy file which conserves most of the difficulties, table's structure, relathionships and modelling.
You will find some quick-calculations / aggregations in the table to evaluate how the measures I've created for this and for another problem I've opened related with ranking purposes, behave.
Note 1: I'm using "Family" from Master_Item_All table, because it helps to get right values on calculations from other fact tables (Budget and Open_Sales).
Note 2: You will see there are three many/fact tables, connected with dimension tables for crossing calculations.
Awaiting for your feedback about this problem and the mentioned in Note 1. Let me know if the link doesn't work, I don't remember very well how to embed a pbix file.
Thanks in advance,:smileyvery-happy:
<iframe width="800" height="600" src="https://app.powerbi.com/view?r=eyJrIjoiMGU3NGQ1YTMtNTJkMi00NDNjLTg1ZmMtYmY2ZTBiYWVhYjBiIiwidCI6IjBkNmJmMDhmLTI3YTgtNDczOS04YWQ1LWQ3YTZkNzRjY2ZjYSIsImMiOjh9" frameborder="0" allowFullScreen="true"></iframe>
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://www.dropbox.com/s/6mjtob5mo7x9gw1/Dummy%20Model.pbix?dl=0
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:
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"
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] ) ) )Best Regards,
Dale