Forum Discussion
Many to Many relationship Standard Deviation Calculation
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
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.
4 Replies
I just edited the question to reflect they are not distinct
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.
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.
