create a pie chart with categories that change with date slicer

[Anonymous]

Hi,

I have data with customer ids where each row represents transaction info. I am supposed to find average transaction per day for each customer. There is a slicer for date where I can choose last 30 days, or last 7 days. According to the chosen dates, avg per day gets changed. If I just use existing table, there exists an issue of finding correct average. For example, a customer did 10 transactions in only 5 days among our chosen 30 days. We get average here 10/5 instead of 10/30. The only way to solve this is by creating a measure using another table called calender, having one to many relationship with date of existing table. It solves the problem.

Now, I need to make a pie chart with categories like less than 0.5 transaction per day on avg, less than 1, less than 2, more than 5 etc. How can I create such categories using just the measure "avg per day". As we can not make a column in the table as avg per day because the column of avg values in the table doesnt change with slicer date (as discussed before eg, last 30 days or last 7 days). I could not figure out how to make categories in pie chart using just a measure here or a table that will change the average values with date slicer. Kindly help me in this!

[tamerj1 · Accepted solution]

Hi @Anonymous 
You can create a segmentation table ('Buckets') that looks like

Segment	Lower Limit	Upper Limit
< 0.5	0	0.5
< 1.0	0	1
< 2	0	2
> 2	2	9999999999
> 5	5	9999999999

You can place Buckets[Segment] in the pie chart along with the following measure

Number of Customers =
AVERAGEX (
    Buckets,
    SUMX (
        VALUES ( Data[Customer ID] ),
        VAR AvgPerDay = [avg per day]
        RETURN
            INT ( AvgPerDay >= Buckets[Lower Limit] && AvgPerDay < Buckets[Upper Limit] )
    )
)