Summary / Weighted Avg as visuals

[robindk]

I have a dashboard that I need to make where the key is to show the weighted avg of completion on a certain things

I have a set of users - with data categories like gender, age, unit +++

Now the wish is to be able to see on a weighted avg - how many course is completed by gender, age groups, unit +++

How can this be done most easily? 

I have 1 table source with all users and their data

another table with completed courses.

They are linked together on the user id

The visuals need to show that by gender Female / Male - the avg number of completed courses i X for example.

So that it gives the right impression. There are alot more males then females in the total. So it needs to be correctly reprensented.

One visual for example is a pie chart, which they want to show the weighted avg completed courses by gender.

Any advice, preferabbly with example  how this can be achieved would be highly appreciated!

[v-shex-msft]

Hi @robindk,

Can you please share some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[robindk]

Hi,

To put it simply

2 tables

Table 1: Employee Master

UserID	Gender	OrgUnit	Country
1	Male	Unit1	US
2	Male	Unit1	US
3	Female	Unit2	UK
4	Male	Unit3	UK
5	Male	Unit3	US

2nd Table:

UserID	TrainingID
1	10
1	11
2	10
3	10
3	11
4	10
5	10

[v-shex-msft]

Hi @robindk,

You can extract the table1 user id list and use it as a parameter to filter table2 records, then you can summarize these records to get the average:

formula =
VAR idlist =
    CALCULATETABLE (
        VALUES ( T1[UserID] ),
        ALLSELECTED ( T1 ),
        VALUES ( T1[Gender] ),
        VALUES ( T1[OrgUnit] )
    )
VAR summary =
    SUMMARIZE (
        FILTER ( T2, [UserID] IN idlist ),
        [UserID],
        "_count", COUNTROWS ( T2 )
    )
RETURN
    AVERAGEX ( summary, [_count] )

Regards,

Xiaoxin Sheng