The average from average

[Anonymous]

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

[v-lionel-msft]

Hi @Anonymous ,

Try this DAX formula:

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

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.

[Anonymous]

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 

[Anonymous]

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

[Anonymous]

Hi @Anonymous ,

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

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

Aiolos Zhao

[Anonymous]

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

[v-lionel-msft]

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

[Anonymous]

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 

[amitchandak]

Not clear, you want to add a new row

For Avg of Avg refer

https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

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]

@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]

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

[SivaMani]

Hi Beck11,

Have you tried my solution for this case?

Are you still facing this issue?

[Anonymous]

Hi SivaMani,

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