Trying to create buckets using a stacked column chart based on mail received.

[kpansia]

Hey, I currently have a bar graph in power-bi. The graph has distinct id's on the x axis and the amount of mail they received on the y axis. In the Table I am using, I have SalesYear, id, and a Val column which has the value 1 in each row. The id shows up multiple times in the table, sometimes more than twice in the same year. The problem is I want the graph reversed. I would like to bucket people based on how much mail they received. Then use a slicer to see how much they receive per year. I have been struggling to find a solution on my own, would anyone have any ideas on how to approach this.
Table l looks like this:

id | salesYear | Val
10 |  2012      |  1
11 |  2012      | 1
11 |  2013      | 1
10 |  2012      | 1
10 |  2013      | 1
12 |  2012      | 1
12 |  2012      | 1
So in the visualization I want to show that on the x-axis that people who received 1 piece of mail = 0, 2 pieces of mail = 2, 3 pieces of mail = 1. My question is how can i achieve this will a Stacked Column chart. Any suggestions would be greatly appreciated!

[Eric_Zhang · Accepted solution]

---

@kpansia wrote:

Hey, I currently have a bar graph in power-bi. The graph has distinct id's on the x axis and the amount of mail they received on the y axis. In the Table I am using, I have SalesYear, id, and a Val column which has the value 1 in each row. The id shows up multiple times in the table, sometimes more than twice in the same year. The problem is I want the graph reversed. I would like to bucket people based on how much mail they received. Then use a slicer to see how much they receive per year. I have been struggling to find a solution on my own, would anyone have any ideas on how to approach this.
Table l looks like this:

id | salesYear | Val
10 |  2012      |  1
11 |  2012      | 1
11 |  2013      | 1
10 |  2012      | 1
10 |  2013      | 1
12 |  2012      | 1
12 |  2012      | 1
So in the visualization I want to show that on the x-axis that people who received 1 piece of mail = 0, 2 pieces of mail = 2, 3 pieces of mail = 1. My question is how can i achieve this will a Stacked Column chart. Any suggestions would be greatly appreciated!

---

@kpansia

AFAIK, there's no too elegant solution, as a workaround, you can create an auxilary bucket table as below. See more details in the attached pbix file.

Then create a measure as

count =
VAR summarizedTbl =
    SUMMARIZE (
        'yourTable',
        'yourTable'[id],
        "mail pieces", SUM ( yourTable[Val] )
    )
VAR maiiPieces =
    COUNTROWS (
        FILTER (
            summarizedTbl,
            [mail pieces] > MAX ( buckets[lower] )
                && [mail pieces] <= MAX ( buckets[upper] )
        )
    )
RETURN
    IF ( ISBLANK ( maiiPieces ), 0, maiiPieces )