Sales Growth Contribution %

Question

Hey everyone,&nbsp;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.&nbsp; However, if it has been solved before, please point me in the right direction.&nbsp;I am looking for a way to calculate the sales contribution in our year-over-year sales growth by state.&nbsp; I have a sample table below where I have the state, current year's sales, prior year's sales.&nbsp; 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.&nbsp;&nbsp;&nbsp;% of Growth = Sales Growth $ / Total Sales Growth $Contribution % = % of Growth * Total Sales Growth %&nbsp;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).&nbsp; 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 %).&nbsp;StateNet Sales CYNet Sales PYSales Growth $Sales Growth %% of GrowthContribution %AZ&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 65,194&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 63,999&nbsp;$&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1,1951.9%(14.6%)0.3%CA&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 62,723&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 55,747&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,97612.5%(84.9%)1.8%CO&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 65,715&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 52,052&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13,66326.2%(166.4%)3.6%NV&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 62,008&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 57,299&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,7098.2%(57.3%)1.2%UT&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 58,696&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 75,522&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (16,826)(22.3%)204.9%(4.4%)VA&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 56,557&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 74,486&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (17,929)(24.1%)218.3%(4.7%)Total&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 370,893&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 379,105&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (8,212)(2.2%)100.0%(2.2%)&nbsp;Any help is appreciated.&nbsp; Thanks.

allisonkennedy · Answer

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 · Answer

AllisonKennedy&nbsp;&nbsp;At the end of the day, I want to be able to chart out, or show in a trend the "Contribution %" column.&nbsp; 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.&nbsp; 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.&nbsp;&nbsp;&nbsp;Hopefully that makes sense.&nbsp;&nbsp;

allisonkennedy · Answer

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.

amitchandak · Answer

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 · Answer

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

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

Forum Discussion

Sales Growth Contribution %

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

Contributing to the Community

Calculate contribution to growth

DAX Growth Contribution

Investment growth

How to make Contribution to Growth formula?