How can I calculate average for multiple columns?

[Anonymous]

I have 100+ columns and need an average for each one but don't want to create 100+ measures to compute the average for each one. Is there a faster way to do this, e.g. can I make just one measure and calculate an average for every column inside it?

[ryan_mayu]

@Anonymous 

maybe you can try to unpivot those columns. 

then you can get different average value by filter the Attribute value.

[Anonymous]

Thanks @ryan_mayu 

I've unpivoted the columns but not sure how I can filter the average by attribute value. I created a measure from the unpivoted column using the below code but this gives an average for all the different columns combined. How do i filter this by attribute now?

average = sum(table[value])/count(table[value])

[ryan_mayu]

@Anonymous 

could you pls provide the sample data and expected output?

[Anonymous]

Hi @ryan_mayu ,

Not sure how to attach excel files as I don't see the option but I have shown a small subset of my data below. I want to create an average for all the value columns, i.e. value_00 and value_01. In my actual dataset this is about 60 columns so will be cumbersome to create a measure for every column.

TagName_00	DateTime_00	Value_00	TagName_01	DateTime_01	Value_01
xxxx	10/01/2022 12:30:00	20	yyyy	10/01/2022 12:30:00	40
xxxx	10/01/2022 13:30:00	30	yyyy	10/01/2022 13:30:00	60

[ryan_mayu]

@Anonymous 

you can select both value column and unpivot columns

if you have 60 value columns, you can select 60 columns and unpivot 

then you can create a measure

average(value)

[Anonymous]

Hi @ryan_mayu ,

I was able to do this but not sure how I can filter this average by attribute. Where is the option located?

[ryan_mayu]

@Anonymous 

What's the expected output based on the sample data you provided?

[Anonymous]

@ryan_mayu ,

If I filter by attribute value_00 then I want the average for value_00, i.e. 25, and the same for value_01. Right now if I take average it will just average the entire column and give 37.5

[ryan_mayu · Accepted solution]

@Anonymous 

pls see the attachment below