aggr in set analysis compare with set value

[ElsKnockaert]

Hello everybody

I have an issue with 

#BC :    =count (distinct Cust_Id)

% Repeat Purchase:    = (count({$}distinct Order_Id) - column(1) )/ column(1)

#Orders : =count({$}distinct Order_Id) 

What I want:

In the column % Repeat Purchase I want to see the % of customers who have had more than 1 order linked to them. 

Current:

Doesn't take into consideration that a customer could also have 3 orders. In which case the % should stay at 2.27%, but now it would be 4.55% .

I've tried (and many variantions) but it's not working:

count({$< aggr(count(Order_Id), Cust_Id)={">1"} >} , Cust_Id)

[OmarBenSalem]

Can you present your date in a simple way? maybe an inline table with 6 lines? and explain with them the result needed? It would be much easier to help when we have data to work with !

Thanks

[ElsKnockaert]

Of course! I hope this helps:

I would like a 3rd column next to #Orders that shows a % of how many distinct customers have had more than 1 order. 

In this example, User1 has 44 distinct customers who have placed an order. But 45 Orders have been made in total, so 1 customer has placed 2 orders. so in the 3rd column I would like to see [amount of distinct customers with multiple orders]  / [amount of distinct customers with at least 1 order = column(1)]. This would be 2.27% in the case of User1.
If I just do (#orders-column(1) ) / column(1) , it doesn't take into account that 1 customer could have placed 3, 4, 5, or more orders. 
If User1 has 44 customers with at least 1 order, but placed 46 orders in total.
Of which 3 at the same customer, then I would like the statement to take that 1 customer with more than 1 order, divide by 44.  Giving 2.27% and not 4.55% as it would with the present statement.

For the other Users, they have as many orders as customers with at least 1 order, so stands to reason that the 3rd should have to be 0 for them or NULL.

[OmarBenSalem]

which information gives you how many orders were placed by a customer?

What I'm interested to know is how your data is created?

Let me try to explain what I've understood :

Employee1 has 5 distincts customers, customer 5 has placed 3 orders , so the total nb of order for Emp 1 would be 7 ! but their was only 1 customer with multiple orders, so 1/5 not 1/7? right?

Employee2 has 5 distincst, but cust 1 and 2 has each 2 orders so the % would be 2/5 not 2/7?

EMployee3 has 2 customers with 2 orders so 0/5?

If this is it, I'll try to create an inline table with this and come back later with an answer (hopefully)

[ElsKnockaert]

That's exactly right!

[OmarBenSalem · Accepted solution]

I'll assume this is it :

your measure would be something like

count(distinct {<CustID={"=count(distinct Orders)>1"}>} CustID)/count(distinct CustID)

which results in :

[ElsKnockaert]

That's it! Thank you so much!