Average of multiple columns selected in a field parameter

[llorvin]

Hi all,

I have a table containing 25 columns. 17 of these columns are used to report answers of 17 questions.  Each column is named from Q01 to Q17. They can contain values from 0 to 5. 

The question is:

How to calculate the average of each line with a field parameter allowing the selection of many or all columns. The aim of this exercise is knowing the interest of each question in the survey depending on a certain population (teachers, students, courses...) I know the average with 17 answers, what if I unselect Q08, for example. 

[Anonymous]

Hi, @llorvin 

I don't think this idea can be implemented in the current version of PBI, as you can observe, the Filed Parameter created has a data type of Text and can't calculate the average value.

You can submit an idea for here and the product team will take note of such a need.

Related Link: New tab (powerbi.com)

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[llorvin]

Hi,

Thanks for your answer, but I don't know if I well understood it. 

What I need, if I use your example, for index 1, a 6th column will display the averagex of all Qxx, so 2,5. We suppose that the field parameter is used in a segment. In the segment, I uncheck Q1. Now, the averageX might be only on columns Q2 to Q4, and the result of the 6th column, index 1, become 1.25. Is that what you understood in my first explanation ?

[Anonymous]

Hi, @llorvin 

IF uncheck Q1, the average is 1.67. Others is the same to your description. Is my understanding what you want?

Best Regards,
Yang
Community Support Team

[llorvin]

Hi, yes you're right. 😂😂😂 I should go back to school or stop working, maybe both.  What I want is 1.67. 

[Anonymous · Accepted solution]

Hi, @llorvin 

You can use unpivot feature to process data in Power Query and do this like following picture in matrix to achieve the need.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIEYmMwjtWJBvOMgNgEiE3BIkZQeVOwWpCICVTeCCISCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Q1 = _t, Q2 = _t, Q3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Q1", Int64.Type}, {"Q2", Int64.Type}, {"Q3", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Index"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[llorvin]

Hi Yang, That's what I've done. I just wanted to know if there was another way to do it in Dax. 

Many thanks for your help.

Ludo