Count based on selection or else show NULL

[richard24best]

Hi All,

I have a query regarding a KPI summary which i was trying to build.

Scenario -

One sheet (Transactions) - Client Name - A, B, C, D, E and reference number - 1,2,3,4,5

Measure - count([reference number)) - giving me - 5 as value (which is fine)

i am trying to link another sheet(Fails) here - Client Legal Name - A1,B1,C1, D1 and reference number - 10,20,30,40

If i don't select anything from Client Legal Name i want the count(Reference number) to show as 0, which means there were no Fails for the Client. But if i select a client, then it should count (reference number) and give me value.

Because sometime when there are no Fails for a Client , the name doesnt appear in Client Legal Name filter so.

Is it possible?

Thanks,

Richard

[Kushal_Chawda · Accepted solution]

@richard24best did you try below

=if( getselectedcount(Client)=0,0,count([reference number)) 

[richard24best]

Hi Kush,

Brilliant it works perfect.

Thank you,

Richard

[richard24best]

@Kushal_Chawda  when you have time please look into my other query  Colour the Range based on values

As i need something similar which i posted in my query, i am managing with median as an option but wont serve the purpose completely. Since you are one of the expert 🙂 appreciate your inputs thanks in advance.

Regards,

Richard

[Kushal_Chawda]

@richard24best  sure will look into that