Trying to Count the number of Appointments in my data
I feel like this should be simple but I'm struggling with how to write the beast mode to where I get a whole number and not an average.
I'm trying to count the total number of appointments in my sales data. This is what I've written:
Count(`Bay Text`)/(COUNT(DISTINCT`Invoice #`))
I've attached a photo showing what the data looks like. Each block outlined in color marks a different invoice (see the Invoice # column). Our data brings over rows of data for each line item on an invoice. Each line item shows the appointment type (Appt Text), bay code (Bay Code), and bay name (Bay Text) for each invoice (this is copied for each line item on an invoice even though its one entry of data for each invoice).
Invoices marked 'No Code' in Appt Text, '0' in Bay Code, and 'No Bay' in Bay Text indicate that that invoice did not have an appointment.
Any help/guidance is appreciated.
ps: I should clarify that I am trying to do this at the Beast Mode level and not the ETL level due to the fact that this data set is used across 1000s of cards and removing duplicates with a group by tile would effect many other cards and I'm not trying to create a separate output dataset for this one card.