Forum Discussion
Calculate average, count same group as 1
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
Hi Anonymous
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/
9 Replies
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.
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.
Would definently unpivot it then. Let me know if you need more detail on that. Happy to help.
Hi Anonymous
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/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.
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
Hi Anonymous
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 _DOutput:
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/
