Many to Many relationship Standard Deviation Calculation

[jonoren]

For the example, I have 2 simple tables: 

1. Countries Table:

CountryID	Name	CurrencyCode
1	United States	USD
2	United Kingdom	GBP
3	Canada	CAD
4	Germany	EUR
5	France	EUR

2. CurrencyExchangeRates Table:

CountryDate	CurrencyCode	Value
1;1/1/2022	USD	1
1;1/2/2022	USD	1
1;1/3/2022	USD	1
2;1/1/2022	GBP	1.2
2;1/2/2022	GBP	1.3
2;1/3/2022	GBP	1.35
3;1/1/2022	CAD	0.7
3;1/2/2022	CAD	0.6
3;1/3/2022	CAD	0.8
4;1/1/2022	EUR	1.02
4;1/2/2022	EUR	1.1
4;1/3/2022	EUR	0.9
5;1/1/2022	EUR	1.02
5;1/2/2022	EUR	1.1
5;1/3/2022	EUR	0.9

And I'm trying to somehow end up with the following countries table:

CountryID	Name	CurrencyCode	Standard Deviation
1	United States	USD	0
2	United Kingdom	GBP	0.076376262
3	Canada	CAD	0.1
4	Germany	EUR	0.100664459
5	France	EUR	0.100664459

I would prefer if the solution would be implemented using Power BI, and if through Power Query then avoiding M code unless it's inevitable. 

Thanks a lot!

Jonathan

[v-yalanwu-msft · Accepted solution]

Hi, @jonoren ;

The relationship as follow:

Then create a measure.

Measure = STDEVX.S(SUMMARIZE('ExchangeRate',[CountryDate],[CurrencyCode],'ExchangeRate'[Value]),[Value])

The final show:

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

[Dhacd]

Hi @jonoren  Can you confirm whether the records in the country table are distinct or not?
Regards,
Atma.

[jonoren]

I just edited the question to reflect they are not distinct

[Dhacd]

Hi @jonoren  Please check whether you are using the countryside from the country table and the country from the exchange table are used to create a relation.

If this post helps, then please consider accepting it as the solution to help the other members find it more quickly.

Thanks and Regards,
Atma.