Forum Discussion

SejalVasani's avatar
SejalVasani
Regular Visitor
8 years ago
Solved

Unable to get bucket data correct

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

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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.

    unable_1

     

    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
    )
)

    unable_2

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

     

    Best Regards,

    Dale

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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.

    unable_1

     

    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
    )
)

    unable_2

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

     

    Best Regards,

    Dale

    • SejalVasani's avatar
      SejalVasani
      Regular Visitor
      7 years ago

      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 :-)