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

[wusaa2222]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[wusaa2222]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[wusaa2222]

Thanks also for this one @ashish highly appreciated!

[Ashish_Mathur]

You are welcome.

[wusaa2222]

Hi all, 

Thank you all for your amazing work. 

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

Best

[Ashish_Mathur]

You are welcome.

[wusaa2222]

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

[grantsamborn]

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.

[ryan_mayu]

@wusaa2222 

here is a workaoround for you

[ryan_mayu]

@wusaa2222 

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