Cumulative Sales Without Dates on Rows

[Ciria]

Dears

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,

[v-jiascu-msft]

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

[Ciria]

Hi @v-jiascu-msft

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,

[v-jiascu-msft]

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]

Hi @v-jiascu-msft

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.

[v-jiascu-msft · Accepted solution]

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

[Ciria]

Hi @v-jiascu-msft

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??
• 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?

[v-jiascu-msft]

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

[Ciria]

Hi @v-jiascu-msft

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...

http://community.powerbi.com/t5/Desktop/Multiple-Levels-Drills-Rank-Measure-Problem/m-p/365971#M1655...

Kind regards,

[Ciria]

Hi @v-jiascu-msft

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.

1. Using your idea with Index, creating a Measure to create cumulative sales when you order by Family name.
2. Using Rankx function and total sales, creating a Measure to create cumulative sales based on Rank or Total Sales.
3. See Report 1 Page to check it out.

Awaiting for your feedback 🙂

Regards,

[Ciria]

Hi @v-jiascu-msft

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.

https://powerbi.tips/2016/10/pareto-charting/

[v-jiascu-msft]

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

[Ciria]

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=eyJrIjoiMGU3NGQ1YTMtNTJkMi00NDNjLTg1ZmMtYmY2ZTBiYWVhYjBiIiwidCI6IjBkN...

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

[Ciria]

Hi @v-jiascu-msft

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,

https://app.powerbi.com/groups/me/reports/6b360cb4-5fa2-4c23-88f2-d6dcb74fd366?ctid=0d6bf08f-27a8-47...

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

<iframe width="800" height="600" src="https://app.powerbi.com/view?r=eyJrIjoiMGU3NGQ1YTMtNTJkMi00NDNjLTg1ZmMtYmY2ZTBiYWVhYjBiIiwidCI6IjBkNmJmMDhmLTI3YTgtNDczOS04YWQ1LWQ3YTZkNzRjY2ZjYSIsImMiOjh9" frameborder="0" allowFullScreen="true"></iframe>

[Jorgast]

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.

[Ciria]

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!