Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
llorvin
Frequent Visitor

Average of multiple columns selected in a field parameter

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. 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

vyaningymsft_0-1718876389522.png

vyaningymsft_1-1718876482703.png

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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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.

vyaningymsft_0-1718690310044.png

 

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

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
Not applicable

Hi, @llorvin 

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

vyaningymsft_0-1718760818770.pngvyaningymsft_1-1718760890669.png


Best Regards,

Yang
Community Support Team

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

Anonymous
Not applicable

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.

vyaningymsft_0-1718876389522.png

vyaningymsft_1-1718876482703.png

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

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.