Forum Discussion
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 leads by date, one table leads by Family, you can switch from one to another using a Bookmark.
Note: Family is a field that groups different items numbers (on this case multiple finished item codes for sale under one single category). Master_Item_All contains all unique Item_Number values, where each family appears multiples times consequently.
Family is needed, therefore, to facilitate figures understanding.
My point is about I am able to create a Comulative Sales by Dates but I am not when I use Family in rows instead.
Total Sales Acumulative 2 =
CALCULATE([Total Sales LY];
FILTER(ALLSELECTED('Date');
'Date'[Date]<= MAX('Date'[Date]))) This is how the second table looks like:
This is how relathionships have been built:
I was trying to use EARLIER function, and even RANK-RANKX Function to do so, but I cannot get the right DAX formula.
May you please help me out?
Thanks in advance,:smileyindifferent:
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
15 Replies
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
New to PowerBI
Would it work if you added the date in the visual filters of your table? Or use the date as part of the Matrix. Where Family and Date are the Row reference and all the other sales data is the column data.
Hi Jorgast
Thanks for the answer, but unfortunately what you suggest don't work.
Your idea of adding Date in Rows would work but the aim to have two tables with different purposes. One helps to evaulate sales by dates and the second; sales by family.
On this second table additional drills are allowed to dive into the data, not finishing in Family, but country_name and customer_name and item_code are defined as additional levels.
As you can see different aims :-)
Thanks anyway for the support given!:smileywink:
