Calculate average per company, not individual answers

[TomStaps]

Hello everyone,

At this moment I am busy with analyzing data in Power BI. My targets are to:

• Calculate the average of column A, B, C separate. Where every company counts as 1. I am not interested in the average of all separate answers.
• The weighted average of all columns (A, B, C). Again, where every company counts as 1. I am not interested in the average of all separate answers.

The problem at this moment is that Power BI shows the average per company in rows, but not in the outcome of the average calculation.

Current (wrong) calculation in Power BI for column A:

• X: 3 + X: 4 + Z: 4 = 11 à 11/3=3,67

Calculations that I need for column A:

• Company X: 3 + 4 = 7 à 7/2= 3.5
• Company Z: 4
• Average = X: 3.5 + Z: 4 = 7.5 à 7.5/2= 3.75
• Blank cells need to be ignored.

Overview data:

Current (wrong) outcome in Power BI:

Outcome I need:

Does someone know how it would be possible to show the right outcome in PowerBI?

Thanks in advance. If you need more explanation, please let me know.

Best Regards,

Tom

[v-yangliu-msft · Accepted solution]

Hi  @TomStaps ,

Here are the steps you can follow：

1. Create measure.

A_Measure =
var _summrize=SUMMARIZE('Table','Table'[Company],"A",AVERAGE('Table'[A]),"B",AVERAGE('Table'[B]),"C",AVERAGE('Table'[C]))
RETURN
IF(
    HASONEVALUE('Table'[Company]),AVERAGE('Table'[A]),AVERAGEX(FILTER(_summrize,[A]<>BLANK()),[A]))

B_Measure =
var _summrize=SUMMARIZE('Table','Table'[Company],"A",AVERAGE('Table'[A]),"B",AVERAGE('Table'[B]),"C",AVERAGE('Table'[C]))
RETURN
IF(
    HASONEVALUE('Table'[Company]),AVERAGE('Table'[B]),AVERAGEX(FILTER(_summrize,[B]<>BLANK()),[B]))

C_Measure =
var _summrize=SUMMARIZE('Table','Table'[Company],"A",AVERAGE('Table'[A]),"B",AVERAGE('Table'[B]),"C",AVERAGE('Table'[C]))
RETURN
IF(
    HASONEVALUE('Table'[Company]),AVERAGE('Table'[C]),AVERAGEX(FILTER(_summrize,[C]<>BLANK()),[C]))

2. Result:

Best Regards,

Liu Yang

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

[lbendlin]

@TomStaps  You have two identical rows  - that will not work in Power BI as it always automatically aggregates.  I have added an index column to mitigate that.

See attached.