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
cristina1128
Regular Visitor

HOW TO CALCULATE SALES DIFFERENCE YEAR ON YEAR and PERCENT TO TOTAL

 

 

2012-06-11_12-54-17_768.jpg

6 REPLIES 6
v-jiascu-msft
Employee
Employee

Hi @cristina1128,

 

Could you please mark the proper answers as solutions?

 

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.

hello, neither answer was correct.  after much research i found the correct formula which is - 

 

dax % ttl: 

=SUMX(tblName, tblName[columnName])/SUMX(ALL([columnName]), tblName[columnName])

Hi,

 

This is an alternative

 

=SUM(tblName[columnName])/CALCULATE(SUM(tblName[columnName]),ALL([columnName]))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

If memory serves you had more than 2 years worth of data represented in that table. wont this solution mean you need to write a year diff % each time you start a new year? The aim should be to make tall tables not wide tables. this gives you the ability to write just the one measure and it will work on all years.

// if this is a solution please mark as such. Kudos always appreciated.
samdthompson
Memorable Member
Memorable Member

 

I think you might need to reformat the data in powerquery to do this a bit more effectively. Right click the elipses to the right of the CUST_RECAP  table and edit query. Once in the query editor, you will want to:

 

  1. unpivot the data- Ribbon/Transform/Unpivot columns. This will make your data tall rather than wide. If sould be about 4 columns. yCAT, xCatName, Attribute and value
  2. make a column called year which extracts out the year from the new column. In this case it would be simple enought to use Ribbon/Transform/Split Column then do by delimiter which would be " - ". This will make 2 columns. One with the year (name it that) and one with the rest of the field name (call this column attribute): xTTL $, xTTL U etc. 
  3. At this point I reccomend that you have a calendar table in your model which i am not sure if you do or not. In the interim format the year column as a date
  4. select the column called attribute and repivot the data using the Ribbon/Transfor/Pivot. choose the column called values as the value.

You should now have a table with xCAT, yCatName, Year, xTTL $, xTTL U, ySHIP $, ySHIPU, zOPPK $, zOPPK U

 

Save and apply changes.

 

Now write some measures:

 

1. TTL XCAT$ =SUM(CUST_RECAP[xCAT $)

2. TTL XCATU=SUM(CUST_RECAP[xCAT U)

etc 

etc 

etc

 

To get the % per category one write something like:

 

=CALCULATE([TTL XCAT$],ALLEXCEPT(CUST_RECAP,yCAT)) / [TTL XCAT$]

 

To get the % change one write something like:

 

=( [TTL XCAT$] - CALCULATE([TTL XCAT$],DATEADD(CUST_RECAP[Year],-1,YEAR))) / 

CALCULATE([TTL XCAT$],DATEADD(CUST_RECAP[Year],-1,YEAR))

 

I would reccomend having a bit of a read of some of the resources on this site to find out how to make the calendar tables since they really are key to the year on year calculations. Also might be worthwhile having a look to see if there is a local PUG group you could go and visit. There could be some people with some good ideas about how to structure your data models in the best ways.

 

Hope it all works out.

 

Cheers,

 

Sam

 

 

 

 

// If this is a solution please mark as such

// if this is a solution please mark as such. Kudos always appreciated.
Anonymous
Not applicable

Hi,

 

So my understanding, you could just create 2 new columns on the query editor.

 

Query Editor -> Add Column -> Custom Column , then you could change the column name and do the calculation there.

 

Hope it helps.

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.