Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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/
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/
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 @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/
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.
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.
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.
Hi Tom,
You could create a calculated table using summarize:
Afterwards, you can create measures for the categories:
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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |