% Weighted Contribution

[Anonymous]

Hello everyone!

I am new to PBI and trying to recreate some calculations I use commonly in MS Excel.

One I am having issues with is % Weighted Contribution (% Cont) to YoY Growth. The formula for this is:

% W Cont = (YTD Sales - PY Sales) / Total PY Sales

Category	PY Sales	YTD Sales	% YoY Gth	GAP	% Cont
Cat A	10	20	100.0%	10	10.0%
Cat B	65	70	7.7%	5	5.0%
Cat C	25	13	-48.0%	-12	-12.0%
TOTAL	100	103	3.0%	3	3.0%

Of course, I want to be able to change or filter category, segment, product, etc. without losing this measure.

Can you please share with me how to do this in PBI? Thanks in advance!

[dedelman_clng]

Hi @Anonymous  - 

A little confused because your narrative doesn't match your attempted formula, but if you wanted this formula

% W Cont = (YTD Sales - PY Sales) / Total PY Sales

It could be written like this

WeigthedYOY =
VAR __AllPYSales =
    CALCULATE ( SUM ( WeightedPct[PY Sales] ), ALL ( WeightedPct ) )
VAR __Delta =
    SUM ( WeightedPct[YTD Sales] ) - SUM ( WeightedPct[PY Sales] )
RETURN
    DIVIDE ( __Delta, __AllPYSales )

If this is not the result you are seeking, please share expected results.

Hope this helps

David