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,
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:
Hi Ciria,
1. The items of Family is still like "Family1, Family2". So I don't know how to add an index in another way.
2. I think accumulating data by other column has the similar solution like "by family".
Best Regards,
Dale
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.
- Using your idea with Index, creating a Measure to create cumulative sales when you order by Family name.
- Using Rankx function and total sales, creating a Measure to create cumulative sales based on Rank or Total Sales.
- See Report 1 Page to check it out.
Awaiting for your feedback :-)
Regards,
I've found this tutorial within PowerBi Tips, and I believe this has to do with our conversation.
Please let me know what you think and I hope it results you handy.