cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Helper I
Helper I

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.

Highlighted
Impactful Individual
Impactful Individual

 

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

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

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

Highlighted

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

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/

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors