Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

The average from average

Hi Folks,

i am a Newcomer in Power BI and i have a question: my table does look like:

 

Projekt, Projectscore, PointA, PointB

A,              66, 67 %, 80, 120

B               31,25 %,  50, 160

 

The Projectscore will be calculated by dividing of fields : PointA / PointB, it is quite simple

my issue: how can i calculate an average of fields Projectscore, my expected output is:

 

Projekt, Projectscore

A, 66,67

B, 31,25

Ø 48, 96

 

Does anybody have any idea, how to solve this issue? thanks a lot in advance

Beck

13 REPLIES 13
v-lionel-msft
Community Support
Community Support

Hi @Anonymous ,

 

Try this DAX formula:

//calculated column
Projectscore = 
VAR x=
DIVIDE(
    [PointA],
    [PointB]
)
RETURN
IF(
    HASONEFILTER([Projekt]),
    x,
    AVERAGEX(Sheet7, x)/2
)

cc4.PNG

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AiolosZhao
Memorable Member
Memorable Member

Hi @Anonymous ,

 

I think @SivaMani 's method is the solution, and if you only want one measure to solve that, you can use the below measure, that works on my desktop.

 

 

Measure = AVERAGEX('Table',DIVIDE(CALCULATE(SUM('Table'[PointA])),CALCULATE(SUM('Table'[PointB])),0))

 

 

Please try.

Aiolos Zhao 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Hi Zhao,

thanks a lot for your responce, my issue does look like:

Project, Aver, PointA, PointB

a, 67,22%,  363, 540

b, 53,61%,  193, 360

c, 69,64%, 195, 280

d, 69,00%, 31, 50

e, 37,22%, 67, 180

f, 61,14%, 107, 175

Total:  60,32%  956, 1585

 

but instead of 58,47% my total avergae is: 60,32%, how can i calculate my averages to have the value 58,47 %

i hope, i explained well, if not, please let me know

 

Hi @Anonymous ,

 

I'm not sure whether you are using my meausre, that's what you want, I try it in my desktop.

 

The average from average.PNG

 

Measure = AVERAGEX('Table',DIVIDE(CALCULATE(SUM('Table'[PointA])),CALCULATE(SUM('Table'[PointB])),0))

 

Aiolos Zhao





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Aiolos thanks a lot for your responce and help, but i am still facing my issue:

i give some more details.

the Field: PointB is a calculated Field: 

PointB = CALCULATE(COUNT(Sheet1[Nr])) * 5
 
and if i use your expression:
Measure = AVERAGEX('Table',DIVIDE(CALCULATE(SUM('Table'[PointA])),CALCULATE(SUM('Table'[PointB])),0))
 
so i have still the wrong result, how can i implemente the calculated part of my expression in your expression, to have 58,47 %
 
Do you have any idea?
Thanks a lot

Hi @Anonymous ,

Has your problem been solved? 

If yes, please consider Accept it as the solution to help the other members find it more quickly.

If not, Please give us a complete example data model and tell us which columns are  from DAX.

 

Best Regards,
Lionel Chen

If the pointB is a measure, you can change the measure to 

 

Measure = AVERAGEX('Table',DIVIDE(CALCULATE(SUM('Table'[PointA])),CALCULATE([POINTB]),0))

 

It shows the correct result in my example, if you can't get the 58.47%, you may need to give more details of your data.

Aiolos Zhao 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Hi amichandak, first of all, thanks a lot for your responce,

i don't want to add a new row, i just want to calculate my average, here is my situation:

my real example does look like:

Project, Aver, PointA, PointB

a, 67,22%,  363, 540

b, 53,61%,  193, 360

c, 69,64%, 195, 280

d, 69,00%, 31, 50

e, 37,22%, 67, 180

f, 61,14%, 107, 175

Total:  60,32%  956, 1585

 

but instead of 58,47% my total avergae is: 60,32%, how can i calculate my averages to have the value 58,47 %

i hope, i explained well, if not, please let me know

SivaMani
Resident Rockstar
Resident Rockstar

@Anonymous,

 

Here you go,

 

Create a measure like below,

Projectscore = DIVIDE(SUM(PointA),SUM(PointB))

Projectscore Derived = AVERAGEX(<Table Name>, Projectscore)

Use Projectscore Derived in the table.

 

Appreciate your kudos!

 

Regards,

Siva Mani

Anonymous
Not applicable

Hi SivaMani,

first of all thanks a lot for your time and responce,

my real example does look like:

Project, Aver, PointA, PointB

a, 67,22%,  363, 540

b, 53,61%,  193, 360

c, 69,64%, 195, 280

d, 69,00%, 31, 50

e, 37,22%, 67, 180

f, 61,14%, 107, 175

Total:  60,32%  956, 1585

 

but instead of 58,47% my total avergae is: 60,32%, how can i calculate my averages to have the value 58,47 %

i hope, i explained well, if not, please let me know

Hi Beck11,

 

Have you tried my solution for this case?

 

Are you still facing this issue?

 

Anonymous
Not applicable

Hi SivaMani,

thanks a lot for your responce, unfortunatelly i am still facing my issue.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.