PowerBI_how to create a column with six cells being the average of six columns accounting filters

[yao_xiao]

Hi everyone,

I have seven columns of data, as shown below.

To create a radar chart, I try to create a new table like the below.

However, I also want the average column to be able to take filters into account. For example, when I select "A" in the Category filter, I want to be able to get the below.

This data is only a sample of a much larger data set. Would it be possible to build a table like this to accommodate multiple filters? Or is there another way to transform data to make a radar chart instead of creating a new table like this? Thank you so much!!!

[ryan_mayu · Accepted solution]

@yao_xiao 

you can select the first column and unpivot other columns in PQ

then you can get the output even don't create measures

pls see the attachment below

[yao_xiao]

Hi Ryan,

Thank you for the response! It is a great solution.

However, as mentioned, I actually work with a much larger data set with a lot more columns which we will use as different filters. And in fact, new rows will be added in over time in the future as well. With the method of transforming data at query editor, we will need to manually transform it each time new data is entered? Please correct me if I am wrong.

Kind regards,

[ryan_mayu]

PQ will record your opertion steps. If the data increases, these steps will be applied to the new data automaticallly. You don't need to manually do the data transfrom.

[yao_xiao]

Got it. Thank you!

[ryan_mayu]

you are welcome