Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
@Anonymous
pls see the attachment below
Proud to be a Super User!
@Anonymous
maybe you can try to unpivot those columns.
then you can get different average value by filter the Attribute value.
Proud to be a Super User!
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])
@Anonymous
could you pls provide the sample data and expected output?
Proud to be a Super User!
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 |
@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)
Proud to be a Super User!
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?
@Anonymous
What's the expected output based on the sample data you provided?
Proud to be a Super User!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |