cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Average of colums in Direct Query

In import mode I just make a new column with the average calculation, but now in Direct Query I cannot use average when making a expression for a new column. What is the best approach to acheive this?

 

Capture.JPG

 

Thanks,

-A-

6 REPLIES 6
Super User IV
Super User IV

@Peavey , You have to think the same in terms of measure.

In case it is simple Avg

Avg Measure =  Average(Table[A])

If that was a calculation at the row level , try like

Avg Measure = AverageX(Table,Divide(Table[A],Table[B]))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandakclose but not quite. 

 

I have 10 rows I need to average. Tried your solution and it works on fex. 2 rows. But when trying all 10, I get the error message: Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your quesry, and try to simplify them.

 

Any ideas?

@Peavey , this a complex formula for 10 rows?

 

How many rows does the table have ?



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandakthere are 10 tables with 62 rows. 

One row is one week. I need the average on each week from each table.

Super User II
Super User II

@Peavey ,

You can't create calculated column in PBI with Direct Query mode. But you can create measure. There are some limitations please refer the below image and link:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about

Capture.PNG

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly : HR Analytics Report , Hotel Management Report, and Sales Analysis Report

@Peavey ,

For more deets, please refer the below link;

https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-direc...

Capture.PNG

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly : HR Analytics Report , Hotel Management Report, and Sales Analysis Report

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors