Calculate average, count same group as 1

[TomStaps]

Hello everyone,

In Power BI I need to make the calculations which you can see below.

- I want to know the average from column A, B and C seperate.

- Each company counts as one. Company x has 4 participants, only 2 filled in a score. Then I calculate 3+4=7 --> 7/2=3.5

- In the case of column A it should be: (3.5+4)/2=3.75.

PowerBI calculate the average from all scores and does not count all answers from 1 company as 1.

I hope you understand me and can give a solution how I can put this calculations in Power BI.

Thanks in advance for you help.

Best Regards,

Tom

[VahidDM · Accepted solution]

Hi @TomStaps 

Try this code for each column, for instance for column A:

Avr. A =
VAR _A =
    SUMMARIZE( 'Table', 'Table'[Company Name], "Avr.", AVERAGE( 'Table'[A] ) )
RETURN
    AVERAGEX( _A, [Avr.] )

Output:

Avr. B = 
VAR _A =
    SUMMARIZE( 'Table', 'Table'[Company Name], "Avr.", AVERAGE( 'Table'[B] ) )
RETURN
    AVERAGEX( _A, [Avr.] )

Avr. C = 
VAR _A =
    SUMMARIZE( 'Table', 'Table'[Company Name], "Avr.", AVERAGE( 'Table'[C] ) )
RETURN
    AVERAGEX( _A, [Avr.] )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[TomStaps]

Hi @VahidDM ,

The calculation is right and works. I really appreciate your help. Now I have another problem. As a follow up I need to calculate the weighted average from column A, B and C. So this time not 1 column, but 3. I tried to multiply your formula, but this I am not able to get it. Can you please help?

Already thanks for your help.

Best Regards,

Tom

[VahidDM]

Hi @TomStaps 

If I understood your request correctly, try this:

Measure = 
Var _A = SELECTCOLUMNS('Table',"Company Name",'Table'[Company Name],"Value",'Table'[A])
Var _B = SELECTCOLUMNS('Table',"Company Name",'Table'[Company Name],"Value",'Table'[B])
Var _C = SELECTCOLUMNS('Table',"Company Name",'Table'[Company Name],"Value",'Table'[C])
Var _D = AVERAGEX(GROUPBY(UNION(_A,_B,_C),[Company Name],"Avr.",AVERAGEX(CURRENTGROUP(),[Value])),[Avr.])
return
_D

Output:

If my posts helps, please consider accepting them as the solutions to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[TomStaps]

Hi @VahidDM , 

I used your calculations in Power BI and I think it works. The outcome of A is correct now. Tomorrow night I will try it on more columns to check it. I will let you know if it works. Already, thank you very much for your input! I appreciate it. 

[bcdobbs]

Would definently unpivot it then. Let me know if you need more detail on that. Happy to help.

[bcdobbs]

Try something like:

Col A Measure =
AVERAGEX (

      VALUES(Table[CompanyId]),

      AVERAGE(Table[A])
)

This iterates over each company in turn and finds their average and then averages the whole lot.

[bcdobbs]

What are A, B and C? If they are categories then you could make things simpler by unpivoting the data in power query first so you end up with a data table of:

Company, Category, Value

Put Company on rows of matrix, Category on columns of matrix and then a measure similar to above in the values.

[TomStaps]

A, B, C are cathegories. 

- The question in the survey was.

- How do you rate the following cathegory (for example A)? Give a score between 1 and 5.

[ValtteriN]

Hi Tom,

You could create a calculated table using summarize: 

Filteredtable = SUMMARIZE('Table','Table'[Company],"A",AVERAGE('Table'[A]),"B",AVERAGE('Table'[B]),"C",AVERAGE('Table'[C]))
Afterwards, you can create measures for the categories:

Average of Category A = AVERAGE(Filteredtable[A]),

Average of Category B = AVERAGE(Filteredtable[B]),

Average of Category C = AVERAGE(Filteredtable[C])

By doing this you can use the created measures to for example rate the categories. I am not sure if I understood what you meant with this but perhaps something lik RANKX could be used to categorize the results.