topic Calculate average, count same group as 1 in DAX Commands and Tips

Calculate average, count same group as 1

Anonymous — Sat, 04 Dec 2021 00:57:00 GMT

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

Re: Calculate average, count same group as 1

bcdobbs — Sat, 04 Dec 2021 07:32:11 GMT

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.

Re: Calculate average, count same group as 1

bcdobbs — Sat, 04 Dec 2021 07:40:55 GMT

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.

Re: Calculate average, count same group as 1

Anonymous — Sat, 04 Dec 2021 07:53:22 GMT

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.

Re: Calculate average, count same group as 1

bcdobbs — Sat, 04 Dec 2021 08:21:06 GMT

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

Re: Calculate average, count same group as 1

ValtteriN — Sat, 04 Dec 2021 11:32:29 GMT

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.

Re: Calculate average, count same group as 1

VahidDM — Sun, 05 Dec 2021 00:38:14 GMT

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/

Re: Calculate average, count same group as 1

Anonymous — Sun, 05 Dec 2021 23:36:17 GMT

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.

Re: Calculate average, count same group as 1

Anonymous — Mon, 06 Dec 2021 22:23:51 GMT

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

Re: Calculate average, count same group as 1

VahidDM — Tue, 07 Dec 2021 22:19:43 GMT

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 _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/