Forum Discussion
Payments for a course
Dear forum
I hope you can help me!
I wish to present calculated sum, avg, min and max of payments for some courses. For each course a participant can participate in a class for a given price. Below I have tried to present the case with table names and variables. Each course is connected to classes by Course ID. The classes are connected to activities by ClassID and activities are connected to payments by ActivityID. I have used colours to exemplify how sum, average, minimum and maximum would be calculated.
Any help would be highly appreciated.
Kind regards,
Jacob
-----------------
Activity table 02 | ||
ActivityID | Date | ClassID |
001 | 11.01.18 | 501 |
002 | 13.04.18 | 502 |
003 | 13.04.19 | 505 |
004 | 13.04.19 | 505 |
005 | 13.07.20 | 503 |
006 | 15.08.21 | 504 |
007 | 15.03.21 | 504 |
008 | 01.04.22 | 501 |
Course table 03 | |
Course ID | Course name |
301 | Course1 |
302 | Course2 |
303 | Course3 |
Class table 04 | |
Class ID | Course ID |
501 | 301 |
502 | 302 |
503 | 303 |
504 | 303 |
505 | 302 |
Payment table 18 | |
ActivityID | Payment |
001 | 100 |
002 | - |
003 | 300 |
004 | 200 |
005 | - |
006 | 100 |
007 | 100 |
008 | 200 |
Anonymous
why min payment for course 2 is not 0? The payment of activity 002 is 0.
pls see the attachment below.
Anonymous
you are welcome.
Actulally the solution is quite simple.
1. create correct relationships among tables.
2. create four measures
sum = sum('Payment table 18'[Payment]) avg = SUM('Payment table 18'[Payment])/CALCULATE(count('Payment table 18'[ActivityID]),'Payment table 18'[Payment]<>0) max = max('Payment table 18'[Payment]) min = min('Payment table 18'[Payment])
7 Replies
- amitchandak
Super User
Anonymous , This is not an ideal schema. You should merge
Merge Table 02, Table 04, Table 03
And Join with Table 04 in power BI
Merge can be done at source or in power bi
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
ryan_mayuAnonymous
why min payment for course 2 is not 0? The payment of activity 002 is 0.
pls see the attachment below.
Dear Ryan
You are absolutely right. I wanted to make a simple example but unfortunately miscalculated, which of course makes it appear confusing.
I assume you have uploaded a solution, which I am grateful for. Unfortunately I can't open it since our PowerBI version is may 2020 and can't be updated for security reasons. I will enclose this information about version in future requests.
Kind regards
- ryan_mayu
Anonymous
you are welcome.
Actulally the solution is quite simple.
1. create correct relationships among tables.
2. create four measures
sum = sum('Payment table 18'[Payment]) avg = SUM('Payment table 18'[Payment])/CALCULATE(count('Payment table 18'[ActivityID]),'Payment table 18'[Payment]<>0) max = max('Payment table 18'[Payment]) min = min('Payment table 18'[Payment])
