cancel
Showing results for
Did you mean:
Frequent Visitor

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

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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

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

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

"Proud to be a Datanaut!"
3 REPLIES 3
Super User

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

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

"Proud to be a Datanaut!"
Frequent Visitor

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

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

Cheers

Super User

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

Pleasure, glad that you learnt something along the way.