Forum Discussion
Group annual amounts by value
Hi everyone,
I work for a small non-profit organisation, reporting on donations received.
What I want to do is, I hope, simple but I cannot work out how to do it.
| aaa | 2023 | 200 |
| aaa | 2023 | 200 |
| bbb | 2023 | 100 |
| bbb | 2023 | 100 |
| bbb | 2023 | 100 |
| ccc | 2023 | 100 |
| ccc | 2023 | 200 |
| ccc | 2023 | 200 |
| ccc | 2023 | 200 |
| aaa | 2024 | 200 |
| aaa | 2024 | 200 |
| bbb | 2024 | 250 |
| bbb | 2024 | 200 |
| bbb | 2024 | 250 |
| ccc | 2024 | 200 |
| ccc | 2024 | 200 |
| ccc | 2024 | 200 |
For each donor I want to add the total donations per year and then group by annual amount. So in this example:
donor aaa donated 400 in each year, bbb donated 300 in 2023 and 700 in 2024, ccc donated 700 in 2023 and 600 in 2024.
My goal is to have 3 ranges of annual donation:
Low = less than 400 in a year
Medium = between 400 and 600
High = above 600
2023 = 1 Low, 1 Medium, 1 High
2024 = 0 Low, 1 Medium, 2 High
Each donation is stored separately in a "Donations" table along with the donor id, date of donation among other fields.
I don't need to report on invidual donors, just the aggregated totals.
Any suggestions gratefully received, as I'm stumped by this.
Thank you in advance,
John
consider using a graphical solution with conditional formatting
Hi johnstrang ,
First you can create a summrize table like this
Donation 1 = SUMMARIZE(Donation,Donation[Donor],Donation[year],"Annual Total",SUM(Donation[value]))second you shoud create a Donation Category Column
Lavel = SWITCH(TRUE(),'Donation 1'[Annual Total]< 400 , "Low", 'Donation 1'[Annual Total] < 600, "Medium","High")and after create a matrix visual
Rows = Year
Columns = Leval
Values =Count of DonorTotal Donar = DISTINCTCOUNT('Donation 1'[Donor])If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
7 Replies
- lbendlin
Super User
consider using a graphical solution with conditional formatting
Hi lbendin
That works - many thanks, but is it now possible to count the occurrences of each colour / category?
If I could do that it would be perfect.
Thanks again,
John
- lbendlin
Power BI does not support dynamic binning so you need to bring your own buckets
Then you can materialize the bucket values and count the ranges.
vivek31Resolver II
Hi johnstrang ,
First you can create a summrize table like this
Donation 1 = SUMMARIZE(Donation,Donation[Donor],Donation[year],"Annual Total",SUM(Donation[value]))second you shoud create a Donation Category Column
Lavel = SWITCH(TRUE(),'Donation 1'[Annual Total]< 400 , "Low", 'Donation 1'[Annual Total] < 600, "Medium","High")and after create a matrix visual
Rows = Year
Columns = Leval
Values =Count of DonorTotal Donar = DISTINCTCOUNT('Donation 1'[Donor])If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
v-saisrao-msftCommunity Support
Hi johnstrang ,
I wanted to check if you had the opportunity to review the information provided by lbendlin . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.- v-saisrao-msft
Hi johnstrang,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
