Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ciria
Advocate III
Advocate III

Cumulative Sales Without Dates on Rows

Dears Smiley Sad

 

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:

 

Family Sales.PNG

 

This is how relathionships have been built:

 

Relathionships diagram.PNG

 

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,Smiley Indifferent

1 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"

Cumulative_Sales_Without_Dates_on_Rows

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] )
    )
)

Cumulative_Sales_Without_Dates_on_Rows2

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

15 REPLIES 15
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

Cumulative Sales.PNG

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.Smiley Very 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"

Cumulative_Sales_Without_Dates_on_Rows

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] )
    )
)

Cumulative_Sales_Without_Dates_on_Rows2

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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??Smiley Surprised
  • 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?Smiley Sad

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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,Smiley Very Happy

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,

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/

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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,Smiley Very Happy

 

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
Resolver II
Resolver II

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!Smiley Wink

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.