Help with defining dynamic ranges for the filter depending on the slicer selection

[Ag1709]

Hi All,

I am fairly new to Power BI and struggling with implementing below scenario.

I have several departments that receive money from different sources. The amount they receive is not consistent every year and some departments receive more than the others. Now I have to create a stacked bar chart to show the money received each year and the also show count of “money gifts” in different ranges like

Year	Range	Count of unique money gift
2012	$0-$100	5
	$101-$500	12
	$501-$1001	2
	And so on
2013	$0-$100	15
	$101-$500	20
	$501-$1001	5
	And so on
2014	$0-$100	7
	$101-$500	13
	$501-$1001	4
	And so on

The challenge is few of the departments received very few gifts in ranges $0-$100 in certain years but received larger gifts in a different year like:

Year	Range	Count of unique money gift
2012	$0-$100	5
	$101-$500	0
	$501-$1001	1
	Over $10000 but < 100K	3
	>1M	0
2013	$0-$100	6
	$101-$500	5
	$501-$1001	5
	Over $10000 but < 100K	1
	>1M	0
2014	$0-$100	8
	$101-$500	0
	$501-$1001	0
	Over $10000 but < 100K	40
	>1M	2

The chart thus created has very small bars for year 2012,2013 but bigger bars for 2014 which makes the smaller values look weird on the bar chart. Something like this:

To approach this, I have created a separate table defining different ranges for each department. The department is the main slicer on the dashboard. My requirement is that when a user selects a particular department, the appropriate range comes as the filter. For example:

Departments	Ranges
Dept a	0-100
Dept a	100-500
Dept a	500-10000
Dept a	10000-100000
Dept a	>1M
Dept b	Greater Than 10001
Dept b	B/w 1001 - 10000
Dept b	B/w 501 - 1000
Dept b	B/w 400 - 500
Dept b	B/w 501 - 600
Dept b	B/w 400 - 500
Dept b	B/w 300 - 399
Dept b	B/w 200 - 299
Dept b	B/w 100 - 199
Dept b	B/w 50 - 99
Dept b	B/w 25 - 49
Dept b	Less than 24

 Can someone guide me in how to approach this issue?

[amitchandak]

@Ag1709 , I think you need buckets on sum(Table[Amount]) means measure

refer

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k