Need to get an individual percentage against another table colum by month and site?

[maowi01]

Still muddiling my way through and appreciate the help:

I have 2 tables with data my first table is by Site and has a row for each month with a total for actual margin.

the next table is by individual- who is assigned to a particular site- who also has a total by month

 I want to see the percentage  they contributed based on the dollars they contributed to the sites overall total by month and display in a line chart

I have the months as filters  and site you can select as visuals

 I have tried a few calulations- Calculated sum with Divide

Something like this:

Var MonthlyEst = [Monthly Margin by # of months]

Var Gaptomarg = SUM('Full View'[Gap to Margin + Forecast])

Return

(MonthlyEst/Gaptomarg) -

none give me the simple result being Employee 1 contributed 1% of the total margin for Nov

Employee 2 = 1% contribution for Oct and less than 1% for Nov

Suggestions appreciated

Branch	Month	Actual Margin
Site 1	10/1/2018	1,162,332
Site 1	11/1/2018	1,165,888
Site 1	12/1/2018	-
Site 1	1/1/2019	-
Site 1	2/1/2019	-
Site 1	3/1/2019	-
Site 1	4/1/2019	-
Site 1	5/1/2019	-
Site 1	6/1/2019	-
Site 1	7/1/2019	-
Site 1	8/1/2019	-
Site 1	9/1/2019	-
Site 2	10/1/2018	261,853
Site 2	11/1/2018	275,629
Site 2	12/1/2018	-
Site 2	1/1/2019	-
Site 2	2/1/2019	-
Site 2	3/1/2019	-
Site 2	4/1/2019	-
Site 2	5/1/2019	-
Site 2	6/1/2019	-
Site 2	7/1/2019	-
Site 2	8/1/2019	-
Site 2	9/1/2019	-

.

Person Name	Site	Period Name	Actual Margin
Employee 1	Site 2	NOV-2018	$                 -
Employee 1	Site 2	NOV-2018	$     1,424.54
Employee 1	Site 2	NOV-2018	$           37.69
Employee 2	Site 1	OCT-2018	$         732.00
Employee 2	Site 1	OCT-2018	$         (29.43)
Employee 2	Site 1	OCT-2018	$           13.58
Employee 2	Site 1	OCT-2018	$           13.58
Employee 2	Site 1	OCT-2018	$         134.16
Employee 2	Site 1	OCT-2018	$     5,392.96
Employee 2	Site 1	OCT-2018	$           15.51
Employee 2	Site 1	OCT-2018	$         (10.36)
Employee 2	Site 1	OCT-2018	$         173.55
Employee 2	Site 1	NOV-2018	$         732.00
Employee 2	Site 1	NOV-2018	$         691.02
Employee 2	Site 1	NOV-2018	$           (6.25)
Employee 2	Site 1	NOV-2018	$           10.01
Employee 2	Site 1	NOV-2018	$         417.71
Employee 2	Site 1	NOV-2018	$         (56.87)
Employee 2	Site 1	NOV-2018	$         (12.14)
Employee 2	Site 1	NOV-2018	$           14.54

[GilbertQ · Accepted solution]

Hi @maowi01

What I did was to first remodel your data so that it was changed into a Fact table with dimension tables. Once this is done it makes the entire model a lot easier to work with and get the required details.

This is what the data model now looks like.

I then create the 3 required measures below which was to work out the right total based on your data

Employee Margin = SUM('Merge1'[Employee Actual Margin])

Branch Margin = CALCULATE(MIN('Merge1'[Site Actual Margin]),ALLSELECTED(Merge1[Branch]))

Difference (%) = 
DIVIDE([Employee Margin],[Branch Margin])

This then allowed me to get the final measure called "Difference (%)" which as shown below has got the right values

As you can see above the calculations are correct.

Here is a link to the PBIX: https://1drv.ms/u/s!Apxn-69XhcAmiOM-J7XfG01CScIG3Q

[maowi01]

@GilbertQ- thank you so much this was extremly helpful. Really appreciate the insight and visual to be able to walk through as well

Cheers

[GilbertQ]

Pleasure, glad that you learnt something along the way.