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
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
Great job, it works!.
1)My first problem now is how to create the index in my real model.
Within my Dummy model, I simplified the names, using "Family 1, Family 2" and so on so forth, but the real model, every family has different caracters length, so we cannot use this approach.
2)The second problem is, this solution works if you arrange data by Family, but you tend to arrange by "Total Sales" (descending), in such case, this doesn't work.
As you could see, Master_Item_All presents unique list of Item_Number, but not unique list of Families, so automatic Index is not gonna work here.
My attempt:
1)What I have done is the following:
- In Family Table, add a calculated column "Total Sales YTD" (important because I have a slicer on the report filtering by 2017)
- Funny thing= I cannot create the measure to filter total sales by current year without writing specifically 2017 or 2018......support??:smileysurprised:
- In Family Table, add a RANKX function to rank all families based in total sales 2017 (or total sales current year...)
- Optional: In Master_Item_All, I've created a calculated column with Related, bringing the Rank Value from Family Table.
- In Key Measures, I create a measure using similar sintax (Calculate + Filter) to create comulative sales.
- You can use "Family" as filter table or "Master_item_All" whether you used "Related" step before.
2) If you want to have cumalitive sales based on Names (alphabetical)
- Arrange by Family Name on Family Table (ascending or descending, at will)
- Create automatic index number, starting in 1.
- Do the steps as mentioned above.
3)Any idea how to deal with this based on "Arrange by Name" or "Arrange by Total Sales" simultaneously? something like IF( _____).
I've uploaded the file for your perusal, awaiting your comments.
https://www.dropbox.com/s/p7kyhp8vt972axl/Dummy%20Model.pbix?dl=0
What do you think?:smileysad:
Hi Ciria,
The download link is broken. Please mask your private info before uploading.
One tip: The index should be added in the table of the 1 side of the relationship 1:*.
Best Regards,
Dale
Here the right link.
Yes you're right, you will see such approach in my file.
https://www.dropbox.com/s/88dtadb0s7m1hpd/Dummy%20Model.pbix?dl=0
I've added a second problem to this dummy file related with RANKX function, very interesting...
Kind regards,:smileyvery-happy:
- In Family Table, add a calculated column "Total Sales YTD" (important because I have a slicer on the report filtering by 2017)