Measure to calculate the total of a column to use as a denominator in a measure

[brycewps]

Hi all,

The measure I want to create calculates the % share each product makes up an individual retail channel and the desired output is a matrix visualization.  My problem is writing a formula the sums each retailer's total to create the denominator in the % share calc.  

I tried 

TY Sales (Static Total) (LW) = calculate(calculate(sum('Channel Sales'[SALES_$]),ALLSELECTED()),'Channel Sales'[Timing]="LW") but it sums total sales.  It works great until I add retailer to the columns in the matrix and then % share is based off the total of all retailers rather than the individual retailer.  I could expand the formula to filter on each retailer, but that requires 3 formulas and will create blanks in my matrix.  Hoping someone has an easy solve.  Thank you.

Data
Week	Timing	Product	Retailer	Sales_$
19	LW	A	Walmart	5
19	LW	A	Amazon	10
19	LW	A	Walmart	5
19	LW	B	Walmart	10
19	LW	B	Amazon	10
19	LW	B	Target	20
19	LW	B	Amazon	10
19	LW	C	Walmart	5
19	LW	C	Amazon	5
19	LW	C	Walmart	10
19	LW	C	Target	20
19	LW	C	Target	20

Desired output in matrix visualization:

Rows: product

Columns: retailer

Values: Sales_$ (LW) and % of Retailer

Current Output									Desired Output
Product	Amazon	% of Retailer	Target	% of Retailer	Walmart	% of Retailer	Grand Total	% of Retailer		Product	Amazon	% of Retailer	Target	% of Retailer	Walmart	% of Retailer	Grand Total	% of Retailer
A	10	7.7%		0.0%	10	7.7%	20	15.4%		A	10	28.6%		0.0%	10	28.6%	20	15.4%
B	20	15.4%	20	15.4%	10	7.7%	50	38.5%		B	20	57.1%	20	33.3%	10	28.6%	50	38.5%
C	5	3.8%	40	30.8%	15	11.5%	60	46.2%		C	5	14.3%	40	66.7%	15	42.9%	60	46.2%
Total	35	27%	60	46%	35	27%	130	100%		Total	35	100%	60	100%	35	100%	130	100%

[v-jianboli-msft · Accepted solution]

Hi @brycewps ,

Please try:

% of Retailer = DIVIDE(SUM('Channel Sales'[Sales_$]),CALCULATE(SUM('Channel Sales'[Sales_$]),ALLEXCEPT('Channel Sales','Channel Sales'[Retailer])))

Final output:

Best Regards,

Jianbo Li

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

[Shaurya]

Hi @brycewps,

You can use the following formula to create the totals column based on Product and Retailer:

Product Total = SUMX('Table',IF('Table'[Product]=EARLIER('Table'[Product]),'Table'[Sales_$],0))

Retailer Total = SUMX('Table',IF('Table'[Retailer]=EARLIER('Table'[Retailer]),'Table'[Sales_$],0))

Hope this helps you in doing the rest. @ me more know if you need further help.

Did I answer your question? Mark this post as a solution if I did!

[brycewps]

Hi @Shaurya - is it possible to use this in a DAX formula/measure?  My dataset is a bit more complex then the example I provided and I'm hoping to avoid bogging down the daily refreshes.  I attempted to input your formula as a DAX measure but received "earlier/earliest refers to an earlier row context which doesn't exist".