GetSelectedCount with sumif

[christianeatman]

Hi,

I am trying to display a KPI for a certain year if no selection is made in the year filter. Specifically, I want to show the sum of an amount for the year 2018 if no selection is made. It seems from my research that GetSelectedCount would allow me to do this. This is what I tried, but does not work:

sum(if(GetSelectedCount([Sheet1.Year]=0) and{<[Sheet1.Year]={2018}>}),Amount)

I have tried this expression in various forms (different placement of commas, different placement of parentheses, placing the sum in a different place), but nothing has worked. Is it possible to accomplish what I want using GetSelectedCount? If not, are there other ways to do this?

Thanks!

[zebhashmi]

Try that

sum({<GetSelectedCount([Sheet1.Year]={'0'}), [Sheet1.Year]={'2018'}>}Amount)

if(GetSelectedCount([Sheet1.Year]=0,Sum({<[Sheet1.Year]={'2018'}>}Amount),Sum(Amount)

[sunny_talwar]

Try this

If(GetSelectedCount([Sheet1.Year]) = 0, Sum({<[Sheet1.Year] = {2018}>} Amount), Sum(Amount))

[zebhashmi · Accepted solution]

Sunny i replied that on my own. but as it many times I see yours reply is there 

You are missing 2018 and I am missing last )

[sunny_talwar]

Hahahaha yes, you are right

[christianeatman]

Thank you both!!

[christianeatman]

Is it possible to do this with a nested aggregation somehow? I would like to use the same logic for a calculation within the GetSelectedCount:

Count([Emplid])/Count(EMPLID_PIT)

Thanks!

[zebhashmi]

can you explain a little bit more what are you looking for

[christianeatman]

I would like to use the same expression as I did with the Sum(Amount):

If(GetSelectedCount([Sheet1.Year]) = 0, Sum({<[Sheet1.Year] = {2018}>} Amount), Sum(Amount))

But instead of Sum(Amount) I want Count([Emplid])/Count(EMPLID_PIT). I receive an error about nested aggregations.

This is what I have tried:

If(GetSelectedCount([Sheet1.Year]) = 0, Sum({<[Sheet1.Year] = {2018}>} [Emplid])/Count(EMPLID_PIT)), Count([Emplid])/Count(EMPLID_PIT))

[zebhashmi]

If(GetSelectedCount([Sheet1.Year]) = 0, (Sum({<[Sheet1.Year] = {2018}>} [Emplid]))/Count(EMPLID_PIT), Count([Emplid])/Count(EMPLID_PIT))

i dont know if you are looking for sum of Emplid or may be count

If(GetSelectedCount([Sheet1.Year]) = 0, (count({<[Sheet1.Year] = {2018}>} [Emplid]))/Count(EMPLID_PIT), Count([Emplid])/Count(EMPLID_PIT))