Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
average of a measure
Formula
Formula Help
Help Requested
  • VahidDM's avatar
    VahidDM
    4 years ago

    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/

     

     

VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
4 years ago

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/

 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    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/