Dividing 2 measure with a greater than

[YcnanPowerBI]

Hello 

I have two measures:

1.  counting orders by customer
   1. _Order Counts = CALCULATE(DISTINCTCOUNT(Merge3[orderId]), ALLEXCEPT(Merge3, Merge3[customerId]))
2. Count of total distinct customers
   1. _Total Distinct Customers = DISTINCTCOUNT(Merge3[customerId])

I am trying to divide the number of customers with an order count greater than 1 by my total distinct customers to get a repeat customer rate.  

Nothing I am try is working 😞

[MNedix]

Hi,

I would re-write the 1st measure like this:

_Orders_over_1 = CALCULATE(
    DISTINCTCOUNT(Merge3[customerId]),FILTER(Merge3,COUNT(Merge3[orderId]>1))

This should give you the Customers with more than 1 Order. The 2nd measure should be fine.

Then you can do the last measure:

_Repeat_Customer_Rate = DIVIDE(_Orders_over_1,_Total Distinct Customers,BLANK())

If this solved your problem then please mark it as the solution so others can see it.

[YcnanPowerBI]

I am receiving the error below and am not sure why

[MNedix]

Since you didn't provide any data I can't really debug it to see what's wrong. Try creating a measure as below, put it in a card visual and see if you get the same.

Temp_measure = calculate(count([orderID]))

[YcnanPowerBI]

My apologies, sample data below.  I did not get the same error, and it matches a prior measure I had for order count.

Here is a link to sample data.  I have never tried sending a link, so please let me know if this does not work.  

Orders Sample Data

[MNedix]

Unfortunately the link doesn't work, so you'll have to use Dropbox or other sharing clouds. However, since you have all columns in the same table, you could re-write the measure as:

_Repeat Counts = CALCULATE(
    DISTINCTCOUNT(Merge3[customerId]),FILTER(Merge3,COUNT([orderId]>1)))

[YcnanPowerBI]

Well this is destined not to work for me for some reason, this one is giving me the same error.  I am including a data screenshot, but I also created a copy in my google drive to see if that link works.  I truly appreciate the time you have already spent on this!  

https://drive.google.com/file/d/1wLaBx8JsAL-4P3E-b4VVsLzZVUkU3L5L/view?usp=drive_link

[MNedix · Accepted solution]

Hi,

I realized I made a mistake in my first measure. Please re-write it as below:

_Repeat Counts = COUNTROWS(
    FILTER(SUMMARIZE(
        'Table','Table'[CustomerID],"Over2",COUNT('Table'[OrderID])),[Over2]>1))

I'm sure this will solve the problem. I tested it with some mock data (see below):

[YcnanPowerBI]

Thank you so much for all of your help!  It worked!