Sales Growth Contribution %

[Claymation]

Hey everyone,

I am a first time poster here, and I have searched for what I am looking for, but haven't been able to find a solution.  However, if it has been solved before, please point me in the right direction.

I am looking for a way to calculate the sales contribution in our year-over-year sales growth by state.  I have a sample table below where I have the state, current year's sales, prior year's sales.  I have a measure calculating the $ and % growth between the two, but I am not sure how to get to the others...where it would be dynamic based on my filtering within the page.  

% of Growth = Sales Growth $ / Total Sales Growth $

Contribution % = % of Growth * Total Sales Growth %

So, in the example table, you can see the sales dollars, and year over year by state, but I want to be able to show in relative terms, what each state contributed to the overall growth (far right columnn).  So, in the example, both UT and VA had the most drag on sales for the time period, which CO contributed the most (sum of all of the contribution % = total sales growth %).

State	Net Sales CY	Net Sales PY	Sales Growth $	Sales Growth %	% of Growth	Contribution %
AZ	$                 65,194	$                 63,999	$                   1,195	1.9%	(14.6%)	0.3%
CA	$                 62,723	$                 55,747	$                   6,976	12.5%	(84.9%)	1.8%
CO	$                 65,715	$                 52,052	$                 13,663	26.2%	(166.4%)	3.6%
NV	$                 62,008	$                 57,299	$                   4,709	8.2%	(57.3%)	1.2%
UT	$                 58,696	$                 75,522	$               (16,826)	(22.3%)	204.9%	(4.4%)
VA	$                 56,557	$                 74,486	$               (17,929)	(24.1%)	218.3%	(4.7%)
Total	$               370,893	$               379,105	$                 (8,212)	(2.2%)	100.0%	(2.2%)

Any help is appreciated.  Thanks.

[Syndicate_Admin]

Hello

The exercise has helped me a lot, thank you!

but can you please explain to me what is the difference between % Growth and % Contribution

[amitchandak]

@Claymation , Try like

% of Growth = divide([Sales Growth $], calculate([Sales Growth $] , allselected(Table))

or
% of Growth = divide([Sales Growth $], calculate([Sales Growth $] , all(Table))

[Claymation]

Here is what I have as the two steps to get me there...however, when I compare to the solution that I have in Excel, it appears to be off.

State % of Total = DIVIDE(

SUM(location_sales_summary_v[net_sales_prev_day]),

CALCULATE(sum(location_sales_summary_v[net_sales_prev_day]),all(location_finance_xref_v[state])))

Then the following is what I am showing in the chart/table.

SSS Contribution % (State) = [State % of Total]*[SSS Growth %]

SSS Growth % is calculated as:

SSS Growth % =

DIVIDE(

    SUM('location_sales_summary_v'[net_sales_prev_day]),

    SUM('location_sales_summary_v'[net_sales_prev_day_prior_year])

)-1

[AllisonKennedy]

This will be tricky due to the negative values for your percentages. Can you specify exactly what result you expect by annotating the screenshot above?

[Claymation]

@AllisonKennedy 

At the end of the day, I want to be able to chart out, or show in a trend the "Contribution %" column.  This allows me to see the overall sales growth for the company of (2.2%), but also allows me to isolate the biggest movers when digging deeper.  So, a particular state may have had a large growth %, but the overall dollars are a small part of the total, so it helps isolate where to dig deeper when analyzing.  

Hopefully that makes sense.  

[AllisonKennedy]

Ah yes, sorry, I see now. So the table is what you want, sorry, I thought that was what you had already achieved. Can you please supply your current DAX that you are using for the Sales LY and measures that you say are working? Also what tables and columns you have in your data model to work with.

Getting the the percentage correct is a matter of finding the right combination of filter functions, such as ALL and ALLEXCEPT

Also, depending on what totals row value you want, you may need to make some slight adjustments.