How to calculate percentage based on the values from two different tables?

[Anonymous]

Hi, 

I have two visuals (from two different tables) on my dashboard. Target and Completion. 

For example : For John the total target is 400.

I want to show a combined visual as such:

If I pick January and John the combined visual will show the following:

(Complete for John in January = 80) / (Total Target of John = 100 + 200 + 100) = 80/400 = 20%

So instead of showing 80 and 400 separately, how can I  show 20% only instead. 

TABLE 1			TABLE 2
Person	Product	Target			Person	Month	Complete
John	A	100			John	Jan	80
John	B	200			John	Feb	100
John	C	100			John	Mar	50
Mary	D	50			Mary	Jan	50
Mary	E	100			Mary	Feb	100
Mary	F	100			Mary	Mar	70

Any help is greatly appcetiated!

Thank you

[Anonymous · Accepted solution]

Not sure if you have a Calendar Table ( if you dont, I'd definitely get one) or a table of unique Persons, but assuming you dont on this example.

Need to create a bridge table of the Person and Month columns:

Bridge = VALUES(Target[Person])

Bridge Month = values( 'Goal'[Month] )

Then since you want the totals to be used for each , I created another table using summarize:

Total Target =
SUMMARIZE (
    Target,
    Target[Person],
    "Total Target", CALCULATE ( SUM ( Target[Target] ) )
)

And make the relationships below, so our data model looks like:

Now we can write the following measures:

Total Goal = sum ( 'Goal'[Complete] )

Sum Total Target = SUM ( 'Total Target'[Total Target] )

% Complete = DIVIDE([Total Goal],[Sum Total Target] )

Then using the Person and Month from the bridge tables as filters we get the following:

Things would be much easier if you have a Calendar and Person tables instead of these bridge tables. Also could do some of this in Power Query to cut down on the heavy lifting DAX has to do, but didnt explain here, but I can if you want.  

[Anonymous]

Hi Nick, 

Thank you for your answer. 

I believe I have those unique tables. I have a date dimension table where I have year-month-week etc. and a separate table for persons. 

And two more tables for the views that I shared with you from before. 

In this case what can I do to get the percentages?

Thanks again!!

[Anonymous]

No need to do the bridge steps then. Just sub in your Date and Persons tables instead.  Then be sure to use fields from those tables as filters.