Group annual amounts by value

[johnstrang]

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

[lbendlin · Accepted solution]

consider using a graphical solution with conditional formatting

[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.

[vivek31 · Accepted solution]

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 Donor

Total 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-msft]

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.

[lbendlin · Accepted solution]

consider using a graphical solution with conditional formatting

[johnstrang]

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

[v-saisrao-msft]

Hi @johnstrang,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

[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.