Formula to count with a specific dimension

[Phara]

I would like to add a KPI in a report that will count the amount of purchase orders but exclusively have to count the ones that has been canceled in the dimension ''HA Desciption Action''

I tried this formula : count(distinct [Num BC] if ([HA Description Action]='Canceled')) but obviously it does not work, I wonder why 😅

Thank you!

[RamanaKumarChintalapati · Accepted solution]

Hi @Phara ,

Will count the amount of purchase orders to exclusively have to count the ones that has been canceled in the dimension ''HA Desciption Action'', for this we can go with following formula’s.

count(distinct (if ([HA Description Action]='Canceled', [Num BC])))

count(If(SubStringCount(HA Description Action,' Canceled ')>= 1, 1))

count({<[HA Description Action]={ ' Canceled '}>}[Num BC])

Hope its will help you.

Thanks!!!

[Ahidhar · Accepted solution]

count({<[HA DESCRIPTION ACTION]={'Cancelled'}>}Num BC)

[Phara]

Thank you so much @RamanaKumarChintalapati !! 

In my dimension ''[HA Description Action]'' their is 3 values : Activ, Canceled, Pending

A purchase order can have 2 statues : Canceled and Pending

I want to exclude the ones that are canceled BUT not Pending.

Do you have a formula for that? 

[RamanaKumarChintalapati]

Hi @Phara ,

Please find the formula for Excluding values:

count(Distinct{<[HA Description Action]-={ “Canceled*”}>}[Num BC])

Thanks!!!