If condition in set analysis

[prasadcm]

Hello All,

So the data is like below

Time Key Relative	ENTTY_ID	ENTTY_NM	New_Product	Total	Prod1	Prod2	Prod3	Prod4	Prod5	Prod6	Prod7	Prod8	Prod9	Prod10	Prod11	test
AUG 2018	Total			10,770.19	1,023.04	568.58	165.80	236.06	947.20	1,738.63	673.49	1,289.48	427.61	225.93	3,474.36	3+
AUG 2018	123	Entity1		6.34	2.02	-	-	-	-	-	0.94	0.32	-	0.01	3.05	1
AUG 2018	456	Entity2		2.65	0.70	-	-	0.11	0.33	0.00	0.38	0.13	-	0.00	1.00	1
AUG 2018	789	Entity3		0.69	0.28	0.00	-	-	-	-	0.05	0.01	-	-	0.34	1
AUG 2018	1010	Entity4		2.79	-0.01	-	0.14	-	-	-	0.20	0.02	0.01	0.01	2.42	1

If the revenue is more than 0.05 in any 3 product for an entity  then entity should be counted  under 3+ category.
I need total of all such entities in text box.

How can I do this in script or UI level?

Regards,

Prasad Mayekar

stalwar1‌

[prasadcm]

stalwar1‌ can you help me with this?

[sunny_talwar]

What is your expression for revenue here?

[Anil_Babu_Samineni]

How about this? Not sure exactly what you are referring? Can you share sample output

Sum(TOTAL <New_Product> {<New_Product = {"=Sum(Revenue)>0.05"}>} Revenue)

[prasadcm]

SUM({$<Measure={'Revenue'},SummaryItem={5},[Time Key Reported] = {'Current Rolling 12 Months'}>}[Rev ex UFR])

expression in table

I need to count entities where revenue is more than 50,000 in any 3 or more product.
So I'm trying to get count in textbox with below expression 

=count(DISTINCT{<[ENTTY_ID] = {"=if(count(DISTINCT {<New_Product={"=if(sum({<[HQ_MNC_Flag]={'HQ'},SummaryItem={5},[Time Key Reported]={'Current Rolling 12 Months'}>}[Rev ex UFR])>50000,1,0)"}>}New_Product)>3,1,0)"}>} ENTTY_ID)

[prasadcm]

I have now attached test data

[sunny_talwar · Accepted solution]

Try this

=Count(DISTINCT Aggr(If(Count(Aggr(If(Sum([Rev UFR]) > 50000, New_Product), [Time Key Relative], [Time Period], ENTTY_ID, New_Product)) >= 3, ENTTY_ID), [Time Key Relative], [Time Period], ENTTY_ID))

[prasadcm]

Thanks a lot,this helped.