Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a 2 tables without relationship with following data:
Table name 2020 (download)
Date | Type | Amount |
2020/01/15 | DF | 100 |
2020/01/19 | VDD | 200 |
2020/01/20 | IUD | 300 |
2020/02/20 | IUD | 150 |
2020/02/23 | IUD | 250 |
2020/02/27 | VDD | 350 |
2020/03/10 | DF | 200 |
2020/03/17 | DF | 300 |
2020/03/18 | VDD | 400 |
Table name 2021 (download)
Date | Type | Amount |
2021/01/31 | VDD | 110 |
2021/01/31 | IUD | 220 |
2021/01/31 | VDD | 330 |
2021/02/28 | VDD | 180 |
2021/02/28 | DF | 300 |
2021/02/28 | DF | 420 |
2021/03/31 | DF | 260 |
2021/03/31 | VDD | 390 |
2021/03/31 | IUD | 520 |
I would like to calculate total of amount by every month (january, february, march) for each year (2020 and 2021) and to calculate ratio between years for every month as follow:
(Total amount of 2020 by month / Total amount of 2021 by month) * 100
and to visualise them (table, crosstab or chart).
Regards
Jan
Solved! Go to Solution.
Hi, @janzitniak
To get the month number in each table:
result:
PBIX:
https://drive.google.com/file/d/15_8-X-ZzxEbu-eIeEamcohl43ykZs8Qo/view?usp=sharing
Best Regards
Zerrick
Did I answer your question? Mark my post as a solution!
You definitely should append those two tables in the query editor (and this disable load on the two source tables), to simplify your analysis and visualization.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you Pat for your approach and idea.
Jan
Hi, @janzitniak
To get the month number in each table:
result:
PBIX:
https://drive.google.com/file/d/15_8-X-ZzxEbu-eIeEamcohl43ykZs8Qo/view?usp=sharing
Best Regards
Zerrick
Did I answer your question? Mark my post as a solution!
Thank you Zerrick for your useful answer, it works as I expected.
P.S.: Is it correct If I create in MONTH table a following measure for calculating ratio (it works in same way as yours):
Ratio = SUM('2020'[Amount])/SUM('2021'[Total])
and then is not neccessary to create measures mTotal2020 and mTotal2021 as in your case.
Jan
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |