Unable to get bucket data correct

[SejalVasani]

Please help me to get State wise Customer's count based on bucket of Sales.

I have 3 table Sales ,Customer, Dim_Dates, now i want state wise count of customers who has sales done or > 0 and differentiate them in bucket.

For Example , I have selected 5,6,7 months of 2018 then first i will calculate sum of sales in this three selected month then devide that by Active Months of Customers from selected month which we have already calculated  in table "CustomerActiveMonths" .

AvgSales = Sum(Invoicedqty)/Active Months of Customer (Count(CalendarYearMonth).)

AvgSales = 300/3 = 100 then calculate the bucket for this i.e it will go to bucket of 50-100.

Then i want state wise Customer Count based on bucket.

See below Output format.
I am also attaching the PBIX file to understood the relation between table.
PBIX file link : https://www.dropbox.com/s/nmds7dx7jcyyivj/Sales_Sample.pbix?dl=0

We have already tried a lot to get output with measure and calculated column.
we are getting proper result when we use Measure but that is not work with Matrix.
and when we use calculated Column then we are getting improper result.

Please help me to get out of this.

Thanks

[v-jiascu-msft · Accepted solution]

Hi @SejalVasani,

Please check out the file in the attachment and test it with your production data. Seems your sample isn't complete, the result looks a little strange.

1. Create a bucket table.

2. Create a measure.

Measure =
SUMX (
    SUMMARIZE (
        'vw_DimCustomer',
        Dim_StateSequence[State],
        vw_DimCustomer[CustomerID],
        "v", [DealerAvg.Sales]
    ),
    IF (
        [v] > MIN ( Buckets[StartOfBucket] )
            && [v] <= MIN ( Buckets[EndOfBucket] )
            || (
                [v] = MIN ( Buckets[StartOfBucket] )
                    && [v] = MIN ( Buckets[EndOfBucket] )
            ),
        1,
        0
    )
)

Link: https://1drv.ms/u/s!ArTqPk2pu-BkhA3X7ThZsaVwnIkm

Best Regards,

Dale

[SejalVasani]

Thanks a lot for this answer...it helped me so much.
Learned new thing to implement bucket logic for which i have worked many hours but didn't get expected result.
But it helps me a lot...Again thank you so much 🙂