Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
wusaa2222
Frequent Visitor

Measure to Dynamically Calculate Number of Customers per Product and Cluster Result

Dear Power BI Community,

 

First of, thank you for the amazing work you create every day.

 

Can you help me with the following?

 

I would like to create a measure which does 2 things:

 

  • Calculate from the Fact Table 'Orders' the number of customers for each product
  • Return the number of products sold in a time frame (I use a date field slider) clustered in the following way:
    • A: 1-5 customers
    • B: 6-10 customers
    • C: 11-infinite customers

 

There is also a linked 'Products' table where products are uniquely listed if that is of any help.

 

Let me know if you need more background information.

 

Appreciate your kind comments.

 

Best regards,

Olin

 

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur 

 

Can you help me again? I would need to have some additional columns (the accumulated quantity sold and the turnover generated + % per quantity / turnover for each cluster) included in the output, can be an additional measure or directly in your cluster measure.

 

Here is your solution file with the updated requirement:

 

https://we.tl/t-1W8bl1yauL

 

Thank you.


Best regards,

Olin

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks also for this one @ashish highly appreciated!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi all, 

 

Thank you all for your amazing work. 

Ashish's solution turns out to work best for my scenario.

 

Best

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
wusaa2222
Frequent Visitor

Hi @ryan_mayu 

 

Thank you for your swift reply.

 

You find a sample data set including expected result here:

 

https://we.tl/t-HQpuveBUjO

 

Best regards

Maybe try something like this:

 

Num of Products in Grouping - 1 step =

CALCULATE(

       DISTINCTCOUNT('Products'[Product Number]),

       FILTER(

              ADDCOLUMNS(

                     VALUES('Products'[Product Number]),

                     "@CustCount",

              CALCULATE(

                     DISTINCTCOUNT('Orders'[Customer]),

                     ALL('Orders'[Product Number]),

                     'Orders'[Product Number] = EARLIER('Products'[Product Number])

              )

              ),

              [@CustCount] >= SELECTEDVALUE('Cluster'[MinCount])

                     && [@CustCount] < SELECTEDVALUE('Cluster'[MaxCount])

       )

)

 

It *seems* to works for me (even with a larger dataset).

The Cluster table needs Min and Max columns.

I'm pretty new at this so good luck with it. 

Also, sorry about the formatting.

@wusaa2222 

here is a workaoround for you





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User
Super User

@wusaa2222 

could you pls provide the sample data and the expected output?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.