Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Help with DAX for average and standard deviation calculation by groups

Good evening experts,

 

I am struggling with a calculation in PowerBI. The scenario is this: I have a list with information of perfomance of different people inside the organization and need to calculate the average and standard deviation. The problem is that these average and standard deviation needs to be calculate inside batches, that are identified by four parameters: Year, Month, Service Desk and group, so for each batch I need to calculate a separate average and standard deviation. I was able to create two separate tables for average and standard deviation with the formulas:

 

Average-Interactions = SUMMARIZE('NAT Matrix','NAT Matrix'[Year],'NAT Matrix'[Month],'NAT Matrix'[ServiceDesk], 'NAT Matrix'[Group],"Average Interactions",AVERAGE('NAT Matrix'[Taken Interactions]))
 
Standard deviation interactions = SUMMARIZE('NAT Matrix','NAT Matrix'[Year],'NAT Matrix'[Month],'NAT Matrix'[ServiceDesk], 'NAT Matrix'[Group],"Standard deviation",STDEV.P('NAT Matrix'[Taken Interactions]))
 
But I am not able to merge both tables so that both are related using four parameters like what you can do with the "Merge" option in Power Queries which I can't do as this are DAX tables.
 
Any suggestion will be very appreciated.
 
Regards,
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    Add "Year, Month, Service Desk and group" in the "Row" field of the Matrix visual,

    Add "Taken Interactions" in the "value" field twice, 

    Select "average" for the first one, "Standard deviation" for the second.

     

    Or create two measures

    average = CALCULATE(AVERAGE('NAT Matrix'[Taken Interactions]),ALLEXCEPT('NAT Matrix','NAT Matrix'[Year],'NAT Matrix'[Month],'NAT Matrix'[ServiceDesk],'NAT Matrix'[Group]))

Standard deviation = CALCULATE(STDEV.P('NAT Matrix'[Taken Interactions]),ALLEXCEPT('NAT Matrix','NAT Matrix'[Year],'NAT Matrix'[Month],'NAT Matrix'[ServiceDesk],'NAT Matrix'[Group]))

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    Add "Year, Month, Service Desk and group" in the "Row" field of the Matrix visual,

    Add "Taken Interactions" in the "value" field twice, 

    Select "average" for the first one, "Standard deviation" for the second.

     

    Or create two measures

    average = CALCULATE(AVERAGE('NAT Matrix'[Taken Interactions]),ALLEXCEPT('NAT Matrix','NAT Matrix'[Year],'NAT Matrix'[Month],'NAT Matrix'[ServiceDesk],'NAT Matrix'[Group]))

Standard deviation = CALCULATE(STDEV.P('NAT Matrix'[Taken Interactions]),ALLEXCEPT('NAT Matrix','NAT Matrix'[Year],'NAT Matrix'[Month],'NAT Matrix'[ServiceDesk],'NAT Matrix'[Group]))

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.