topic Re: Group annual amounts by value in DAX Commands and Tips

Group annual amounts by value

johnstrang — Mon, 14 Apr 2025 14:01:27 GMT

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

Re: Group annual amounts by value

lbendlin — Mon, 14 Apr 2025 14:56:09 GMT

consider using a graphical solution with conditional formatting

Re: Group annual amounts by value

johnstrang — Mon, 14 Apr 2025 15:59:07 GMT

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

Re: Group annual amounts by value

lbendlin — Mon, 14 Apr 2025 17:31:12 GMT

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.

Re: Group annual amounts by value

v-saisrao-msft — Tue, 15 Apr 2025 10:45:22 GMT

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.

Re: Group annual amounts by value

vivek31 — Tue, 15 Apr 2025 12:13:58 GMT

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.

Re: Group annual amounts by value

v-saisrao-msft — Mon, 21 Apr 2025 04:22:00 GMT

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.

Re: Group annual amounts by value

v-saisrao-msft — Thu, 24 Apr 2025 06:38:54 GMT

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.