topic Re: Summary / Weighted Avg as visuals in DAX Commands and Tips

Summary / Weighted Avg as visuals

Anonymous — Mon, 13 Jun 2022 14:02:28 GMT

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!

Re: Summary / Weighted Avg as visuals

Anonymous — Thu, 16 Jun 2022 03:04:10 GMT

Hi Anonymous,

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

Re: Summary / Weighted Avg as visuals

Anonymous — Mon, 20 Jun 2022 09:06:15 GMT

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

Re: Summary / Weighted Avg as visuals

Anonymous — Fri, 15 Jul 2022 07:17:19 GMT

Hi Anonymous,

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