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/

     

     

bcdobbs's avatar
bcdobbs
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        ValtteriN
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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.