Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
try this
=Count({<[Sub Sectors] ={"=(Sum({$<Id={6}>}[Market Size Values])/Sum( total {$<Id={6}>}[Market Size Values]))<=0.8"}>} DISTINCT [Sub Sectors])
Thanks Kushal for your help but, it's giving me the total count of Sub Sectors.
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] )
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])