Display count of Subsectors which acquires 80% of the Market in a Text object

[#1]

Hi Everyone,

I want to display the count of Subsectors acquiring 80% of the Market in a text object.

There's a unique id which defines the Market Size for e.g., Id=6. If i use this in a set expression, it gives me the market size values corresponding to the respective sub sectors.

Dimension- [Sub Sectors]

Set Expression: Sum({$<Id={6}, Year={2017}>}[Market Size Values])    (for the current year). This gives me the result as shown in the below table:

Sub Sectors	Market Size Values
Sub Sec1	1000
Sub Sec2	2000
Sub Sec3	3000
Sub Sec4	4000
Sub Sec5	5000

But, if i've to display the count of subsectors which acquires 80% of the Market in a text object, how can we achieve this? Please advise.

Thanks,

Zain Shaikh

[Kushal_Chawda]

try this

=Count({<[Sub Sectors] ={"=(Sum({$<Id={6}>}[Market Size Values])/Sum( total {$<Id={6}>}[Market Size Values]))<=0.8"}>} DISTINCT [Sub Sectors])

[#3]

Thanks Kushal for your help but, it's giving me the total count of Sub Sectors.

[#4]

I'm trying below expression. Kindly check if it's correct or not and let me know.

=Count({<[Sub Sectors] ={"=RangeSum(Above(Sum([Market Size Values]),0,RowNo()))/Sum(TOTAL [Market Size Values]) <= 0.8"}>} DISTINCT [Sub Sectors] )

[Kushal_Chawda]

try this

Count({<[Sub Sectors] ={"=(Sum({$<Id={6}>}[Market Size Values])/Sum( total  <[Sub Sectors]>{$<Id={6}>}[Market Size Values]))<=0.8"}>} DISTINCT [Sub Sectors])