Calculate the average of values across several columns in same row

[Anonymous]

I'm trying to calculate the average across serveral columns of data but a single row, but the examples i've looked it it only involves columns

 

So for above i would like the average for the country of Albania from 1995-2000 and the result shown in a seperate column called Average by Country. Been a novice to Power BI i thought this could be achieved using Quick Measure.

Why can't you select rows in Power BI and only Columns???. I was thinking you could select a row then use New Quick Measure

[v-piga-msft]

Hi @Anonymous,

By my tests, both of solution from affan and AlB should be helpful. If you will have more column for year, the solution of AlB will be more simpler.

If you use the solution from AlB, you could create the measure with the formula below.

Measure =
CALCULATE (
    AVERAGE ( Table2[Value] ),
    ALLEXCEPT ( Table2, Table2[Country Name], Table2[Country Code] )
)

In addition, for your scenario, if you want to unpivot columns for the year columns, please replace the null value to 0, otherwise it will ignore the null value after unpivot columns.

If you have solved your problem, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[Anonymous]

Its clear i need to scrub up on the power of DAX

After Unpivot the table as @AlB suggested I used the measure @v-piga-msft suggested. My version below

Measure = CALCULATE ( AVERAGE('Tourism Numbers'[Value] ),ALLEXCEPT('Tourism Numbers','Tourism Numbers'[Country Name],'Tourism Numbers'[Country Code]))

i get the warning: The AVERAGE function only accepts a column reference as an argument.

In this instance I can confirm @affan method is indeed the quickest way to solve this solution, but none the less i'm intrested in getting the Unpivot Scenario working as it seems for want of a better word cleaner should more data be added at a later date

[affan · Accepted solution]

Hi @Anonymous

You can create a calculated column

Average by Country = VAR _Total=[1995]+[1996]+[1997]+[1998]+[1999]+[2000]
var _avg= _Total/6
return 
_avg

You ca find the sample pbix file here

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

[AlB]

Hi @Anonymous

I think it would be best to restructure the data in your table. Use the Pivot/Unpivot feature inthe query editor to get the table in the form shown below. Then you can do what you are looking for in a much more convenient way. 

Country          Code      Year         Value

Albania            ALB       1995        304000

Albania            ALB       1996        287000

Albania            ALB       1997        119000

...

Afghanistan     AFG       1995