Shaping data and calculate weighted average

 

Here's example code for normalising your raw data in PQ:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PU3BU0lEyMgISbkBsCOIAsTEQmyjF6sBVOYEkDKGqjJFUoqhyBgmaQlWZoKhEUuUCEjADEr5QMwzhqpFUuYIkzZFUmUBVoqhyg7neFy4Ld1csAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Age = _t, Gender = _t, #"Q1. Family" = _t, #"Q1. Money" = _t, #"Q1. Health" = _t, #"Q1. Friends" = _t]),
    unpivOthCols = Table.UnpivotOtherColumns(Source, {"Name", "Age", "Gender"}, "Option", "Response"),
    addWeight = Table.AddColumn(unpivOthCols, "Weight", each (4 - Number.From([Response])) + 1, type number)
in
    addWeight

It's literally just unpivoting and adding a semi-hardcoded [Weight] column.

In a production model, you would further normalise this by removing all the 'Person' fields except the person identifier (in this case [Name]), and using a related dimension table for the person information fields (in this case [Age] and [Gender]). There's an example of this in the working PBIX I've attached below if you want to take this extra step (you should).

 

Once you have your data in this efficient format, you just need a few small measures:

// Count the number of responses:
_noofResponses = COUNTROWS(factTable)

// Get the sum of weight value
_sumWeight = SUM(factTable[Weight])

// Calculate weighted average:
_weightedAvg = DIVIDE([_sumWeight], [_noofResponses], 0)

 

Example Output:

 

The beauty of setting it up like this is that you can now calculate any of your measures over any dimensions, so you're not stuck with a single hard-coded data format. It's also very fast and HDD size efficient.

 

Let me know how you get on.

 

Pete

 

Yes, that's possible. Use this in your [Weight] column instead:

( Number.From(List.Max(previousStepName[Response])) - Number.From([Response]) ) + 1

 

Pete