How to caulated average in Power BI

[baron33]

This is how my data set looks like (it has some duplication but very hard to delete -- so I want to try 'calculated average'

Like the following: 

The real average should be 10+5+20

but if I use the category average I belive it is:

(10*2+5*3+20) /(4A+3B+1C)

if no sales, then product should not be count

Product A              Sales

A                              10

A                              10

A                           null

A                           null

B                             5 

B                             5

B                             5

C                            20

[Ashish_Mathur]

Hi,

What result do you want to see = 10+5+20=35 or 35/3=11.67?

[baron33]

This haha 10+5+20=35

[Ashish_Mathur]

Hi,

Drag this measure to a card visual

=SUMX(VALUES(Data[Product]),MIN(Data[Sales]))

Hope this helps.

[v-lili6-msft · Accepted solution]

hi, @baron33 

what you want (The real average should be 10+5+20) may be a measure totals problem. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

For your case, just try this formula

Measure = SUMX(VALUES('Table'[Product]),CALCULATE(AVERAGE('Table'[Sales])))

or JosefPrakljacic's solution may meet your requirements.

here is sample pbix file, please try it.

Regards,

Lin

[JosefPrakljacic]

Hi @baron33 ,

does this help you? (I'm not quite sure if I have understood you correctly)

Average Disinct = AVERAGEX(DISTINCT('Table'[Sales]),'Table'[Sales])

If this post was helpful may I ask you to mark it as solution and give it a 'thumbs up'? This will also help others

Have a nice day!

BR,
Josef
Graz - Austria