How count a number of values returned by an expression

[paul_ripley]

Hello

I have an expression that I am using in table (see word document)

=round(sum({
<period ={">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}, pl -={"X01*"}, bussline_abrev = {PE}
>}[Investment GBP]))

It shows Office Location, Portfolio and Value

I will collapse the Portfolio, all I want to see is Office Location plus the number of portfolios in each company.  I assume this has something to do with the Count function.  Eg Benelux wold show 6 and France 1 and so on

[sunny_talwar]

So, when expanded, you want to see the sum of value and when collapsed, you want to see count??

[paul_ripley]

Hi

Here is the table I am trying to copy

[sunny_talwar · Accepted solution]

How about this?

=Count(DISTINCT {<period ={">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}, pl -={"X01*"}, bussline_abrev = {PE}>} portfolio)

Not sure if portfolio is the name of the field or if it is just a label, but make sure to use the name of your second field in the table from the 1st screenshot 

[paul_ripley]

great thanks

I amended it slightly,  so to only sure where values > 0 and then did dimension limits to restrict where values > 0

But many thanks for your help, it is appreciated

If(
round(sum(
{
<period ={">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}, pl -={"X01*"}, bussline_abrev = {PE}
>
}
[Investment GBP])
)
>0,

Count(DISTINCT {<period ={">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}, pl -={"X01*"}, bussline_abrev = {PE}>} portfolio), '')