Aggregation of 3 tables for percentage as output

[Praj]

Hello Everyone! 

I have been stuck with a bit complicated problem and looking for some help. I have 3 tables as shown below:

Table 1:  Each user is tagged to a type which he/she belongs to 

Table 2:  Each type has certain activities under their buckets. For ex, Type 1 has Activity 1,2,& 3. Type 2 as only Activity 2 &3

Table 3:  This table provides information about user and the activities undertaken by them and the date when they took an activity

The above 3 are the input tables. 

What I am looking is to identify the % of activities taken by the users which are inline with the users type as shown below.

For ex, User 1 had taken 3 activities - Activity 1,3 and 1 again. All three activities are part of his type-Type 1 and hence the output says 100% of activities are part of his type. Similarly, User 2 had taken 3 activities- Activity 2,1, and 4 however only activity 2 is part of user 2's type and hence the output says-Only 33% of activities taken by User 2 is inline with his type.

I am trying to find a way in Power BI to automate this problem, any solution or any approaches for this problem is highly appreciated.

Thanks in advance!

Cheers,

Praj 

[v-zhangti · Accepted solution]

Hi, @Praj 

You can try the following methods.

Measure = CALCULATE(COUNT(Table3[Activities]),ALLEXCEPT(Table3,Table3[User]))

Measure 2 = 
VAR _user = SELECTEDVALUE(Table3[User])
VAR _type = CALCULATETABLE(VALUES(Table1[Type]),Table1[User]=_user)
VAR _activity = CALCULATETABLE(VALUES(Table2[Activities]),'Table2'[Type] IN _type)
RETURN
CALCULATE(COUNT(Table3[Activities]) ,Table3[Activities] IN _activity)

% = DIVIDE([Measure 2],[Measure])

User4 has Activity 1, User4 corresponds to Type3, there is Activity 1 in Type3, and User4 should also be 100%.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Praj]

Hi @v-zhangti ,

Thanks for the solution. I was wondering is there any way to add date as a slicer and the values gets updated as per the date slicer. 

For example, If I choose Feb 2022 in slicer, for user 2 the % should be 100% as he had only one activity in february and it is aligned with the activity type

Any approach for this problem will be extremely helpful.

Cheers,

Praj