Weighted Average Data Model

rlenahan · ‎10-07-2022

I have a few hundred data sources coming into PowerBI (20-ish Excel SpreadSheets, each with lots of tabs). The incoming data all looks like this:

Questions	Vendor 1	Vendor 2	Vendor 3
Section 1 (S1)	4	2	1
S1, Question 1	0	1	5
S1, Question 2	4	1	2
Section 2 (S2)	3	4	0

Essentially the project is as follows:

- There are 4 key areas (Finance, CLM, HCM, and P2P).

- Each key area has a number of spreadsheets associated with it.

- These spreadsheets are broken up by sections within the area. For example, CLM has 7 sections.

- Within each section of each spreadsheet are questions. These questions receive grades of 0 - 5.

My goal is to calculate a weighted average of Vendors for both the Sections within a spreadsheet and the Key areas.

CLM has 7 sections with 24 questions spread among each section, but not evenly. A weighted average needs to be applied here so that each section is weighted equally despite some having more questions than others.

Because the 4 key areas have varying volumes (CLM has 24 questions total, Finance has 130 questions total), these all need another weighted average applied upon the previous one to balance them across the 4 key areas.

The end goal will be tables that display weighted average numbers for each Vendor at two levels. The first is a view and roll-up of how a vendor scored within a key area (ie: Vendor 1 scored a 2.5/5 in CLM and here is how they ranked in each of the sections). The second is a table of consolidated weighted averages for the Key areas (ie: Vendor 1 scored a 4/5 among CLM, 2/5 in Finance, etc.)

I've got all the data coming into PowerBI and aggregated all into one Query. I've created Measures to calculate and apply weights (each key area being equal among the 4 despite some having more questions than others) but this ignores weights at the Section level.

So I created Queries to combine data on the Section level and Measures to apply weights within the sections, but then I can't get this to roll up to the key area tables.

How do you recommend I build this data set? Below are a few screenshots of how the two outcomes should look, but I can't seem to get the weighted averages applied to everything appropriately.