condition in count with variable

[Christinedv]

I am trying to build a measure in a table that calculates the ratio of Count(ClientID) in the numerator to a filtered count in the denominator. Specifically, I want to filter the denominator based on a variable where the count of ClientID exceeds a certain threshold ($(vMin_Purchase)), but I'm having trouble using this condition inside set analysis.

Here is the expression I'm trying to use:

=if( 
Count(ClientID) >= $(vMin_Purchase) 
and Count(ClientID) > 0, 
Count(ClientID) / 
Count(Total <Product, Manufacturer> ClientID),
Null() 
)

I want to make so that the denominator only takes the clients above a x amount of purchases.
I wanted to do something like :

=if( 
Count(ClientID) >= $(vMin_Purchase) 
and Count(ClientID) > 0, 
Count(ClientID) / 
Count(Count(ClientID) >= $(vMin_Purchase) Total <Product, Manufacturer> ClientID),
Null() 
)

but that is not allowed, as it is a nested aggregation.
can someone help me, how i can set the condition so my denominator is calculated the right way?
hard coding all the possible combinations of product and manufacturer is not an option.

[marcus_sommer]

It's a bit confusing and I could imagine that you don't need a nested aggregation for the normal calculation by using something like this:

if(sum(Purchase) >= $(var), count(ID) / count(total ID))

which may be working on the row-level but not on the totals. For the total the nested aggregation could be performed with an aggr-wrapping like:

avg(aggr(if(sum(Purchase) >= $(var), count(ID) / count(total ID)), Dim1, Dim2))

[marksouzacosta]

Hi @Christinedv,

Not sure if this is what you are looking for but you can use some advanced Set Analysis expressions in your Measures to find your numbers.

In the example below, I created a KPI to count customers with sales over U$ 12k:

This is the expression:

Count(DISTINCT {<CustomerKey = {"=Sum(SalesAmount) > 12000"}>} CustomerKey)

Regards,

Mark Costa

[Christinedv]

Thank you for the responses!

I have a table that contains

row	count(client)	City	Product	Manufacturer	chance
1	23	1	A	AA	4.9%
2	156	1	A	BB	33.4%
3	223	1	A	CC	47.8%
4	65	1	A	DD	13.9%

My variable is about the minimum amount of times clients have made that purchase.
If I raise the variable to 50, row 1 is not shown because it is not equal or bigger than the variable.
But for the measure of the chance, it still takes the row in to account when i use TOTAL. I want the rows that are shown to add up to 100%.

[marcus_sommer]

It shouldn't happens if you apply - adapted versions - of the above suggestions. Helpful would be if you not only attempts to implement they completely else also parallel in parts to see if they work like expected.

Important is to set the conditions of: if(Exp >= $(var), ... within the needed context - which seems to be your object row-level. The total is independent from it because it didn't touch the underlying/adjusted data-set else is just a statement which object-dimensionalities should be considered - none with a pure total respectively only those which were listed like <Dim1, Dim2>

[Chanty4u]

Try this 

Sum(

    IF( Count(client) >= $(vMinClients) , Count(client) )

)

/

Sum(

    TOTAL

    IF( Count(client) >= $(vMinClients) , Count(clien

t) )

)

[Nagaraju_KCS]

May be this 

=IF(Count(ClientID) > 0,
           Count(ClientID)

           /
           Count({<ClientID = {"=Aggr(Count(ClientID), ClientID) >= $(vMin_Purchase)"}>}TOTAL ClientID),
       Null()
      )