Help with DAX for average and standard deviation calculation by groups

[Anonymous]

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,

[v-juanli-msft · Accepted solution]

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.