Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |