cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
maowi01 Frequent Visitor
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

 

BranchMonthActual Margin
Site 110/1/2018           1,162,332
Site 111/1/2018           1,165,888
Site 112/1/2018                           -  
Site 11/1/2019                           -  
Site 12/1/2019                           -  
Site 13/1/2019                           -  
Site 14/1/2019                           -  
Site 15/1/2019                           -  
Site 16/1/2019                           -  
Site 17/1/2019                           -  
Site 18/1/2019                           -  
Site 19/1/2019                           -  
Site 210/1/2018               261,853
Site 211/1/2018               275,629
Site 212/1/2018                           -  
Site 21/1/2019                           -  
Site 22/1/2019                           -  
Site 23/1/2019                           -  
Site 24/1/2019                           -  
Site 25/1/2019                           -  
Site 26/1/2019                           -  
Site 27/1/2019                           -  
Site 28/1/2019                           -  
Site 29/1/2019                           -  

.

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

Accepted Solutions
Super User
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.

 

image.png

 

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

 

image.png

 

As you can see above the calculations are correct.

 

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



Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"
3 REPLIES 3
Super User
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.

 

image.png

 

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

 

image.png

 

As you can see above the calculations are correct.

 

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



Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"
maowi01 Frequent Visitor
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
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.


Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"