Am I using the proper function for this problem?

[neena123]

I have four facilities that are joined together under the dimension name Facility. In order to separate each facility to show the proper data I have to write the expression like this:

count(Distinct if ([Turnaround Days] <=0 AND [Status]='Shipped' AND  [Facility]='Alpharetta',[Unique Order key], null()))

That expression does work for the individual charts. However I want each facility to have two percent under each chart showing this year turnaround day times that are over 3+ days and last year 3+ turnaround days. Then I would like a text object like an arrow pointing up or down if the number has gotten higher or lower. This is how I have the expression written:

=count(distinct if([Turnaround Days]>2 AND [Facility]='Alpharetta' AND [Status}='Shipped', [Unique Order Key], null()))/count(Distinct [Unique Order Key])

It is not giving me the average of the Alpharetta facility it is dividing it by ALL of the facilities turnaround Days that is over 2 but I want it to just stay in the facility I have assigned it too. So I started to write the expression like this:

=count(distinct if([Turnaround Days]>2 AND [Facility]='Alpharetta' AND [Status]='Shipped, [Unique Order Key], null()))/count(Distinct [Unique Order Key] AND [Facility]='Alpharetta')

But it doesn't give me a percent just a random number that I know is not correct.

I have attached a photo of the layout. The blue box is the percent of Turnaround Days >2. I have not set the year yet either and I need to know how to incorporate that into the expression as well. I want two percent under each facility like I had said before one for current year and one for last year.

How can I make this work?

[Nicole-Smith · Accepted solution]

Your first expression can be written better using set analysis, and then we can also fix the second part:

=count({<[Turnaround Days]={'>2'},[Facility]={'Alpharetta'},[Status]={'Shipped'}>}distinct [Unique Order Key])/count({<[Facility]={'Alpharetta'}>}Distinct [Unique Order Key])

[neena123]

THANK YOU!

[neena123]

Thank you for helping me. I have another question I have been trying to make the expression show current year and previous year how would I incorporate that into the expression? Is their a current year function?

[Nicole-Smith]

Current Year:

=count({<[Turnaround Days]={'>2'},[Facility]={'Alpharetta'},[Status]={'Shipped'},[Date Field]={'=year([Date Field])=year(today()))'}>}distinct [Unique Order Key])/count({<[Facility]={'Alpharetta'},[Date Field]={'=year([Date Field])=year(today()))'}>}Distinct [Unique Order Key])

Previous Year:

=count({<[Turnaround Days]={'>2'},[Facility]={'Alpharetta'},[Status]={'Shipped'},[Date Field]={'=year([Date Field])=year(today())-1)'}>}distinct [Unique Order Key])/count({<[Facility]={'Alpharetta'},[Date Field]={'=year([Date Field])=year(today())-1)'}>}Distinct [Unique Order Key])

Note that [Date Field] cannot be a dimension on your chart.

[neena123]

This is what I have: =count({<[Turnaround Days]={'>2'}, [Facility]={'Alpharetta'}, [Ship Year]=('today())-1', [Status]={'Shipped'}>}distinct[Unique Order Key])/count({<[Facility]={'Alpharetta'}>}Distinct [Unique Order Key])

[neena123]

lol I was writing a reply before I saw this. Thank you so much for your time!!