Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sum all values dependend on value on another column

Hi all,   I have a large database of entered Hours. And I want to calculate the overall average hourRate using an ALL measure so I can show the overal average hourrate in comparison per employee/pr...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    Hi Anonymous 

     

    You can create a measure and place it in a card visual for instance.

    If you want the average per line:

     

    Measure =
AVERAGEX (
    FILTER ( ALL ( Table1 ); Table1[Hour ID] = 1 );
    DIVIDE ( Table1[Sales Amount]; Table1[Amount Hours] )
)

     

    If you want the weighted average (by number of hours):

     

    Measure =
DIVIDE (
    CALCULATE ( SUM ( Table1[Sales Amount] ); Table1[HOURS ID] = 1; ALL ( Table1 ) );
    CALCULATE ( SUM ( Table1[Amount Hours] ); Table1[HOURS ID] = 1; ALL ( Table1 ) )
)
AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

Hi Anonymous 

 

You can create a measure and place it in a card visual for instance.

If you want the average per line:

 

Measure =
AVERAGEX (
    FILTER ( ALL ( Table1 ); Table1[Hour ID] = 1 );
    DIVIDE ( Table1[Sales Amount]; Table1[Amount Hours] )
)

 

If you want the weighted average (by number of hours):

 

Measure =
DIVIDE (
    CALCULATE ( SUM ( Table1[Sales Amount] ); Table1[HOURS ID] = 1; ALL ( Table1 ) );
    CALCULATE ( SUM ( Table1[Amount Hours] ); Table1[HOURS ID] = 1; ALL ( Table1 ) )
)
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanx for your answer!