Forum Discussion
Billing Accuracy %
Yea, I think that's right. You're essentially joining two fact tables together only on "sold to," which I believe represents the grain of a Customer. First, this is going to create a many-to-many relationship between those two tables. Second, it's not taking into account when the transaction occured. So, your distinct counts of corrections are always going to represent the total for that "sold to" number, regardless of the month selection. Ideally you'd be able to tie each correction back to the exact order that is corrected, which was the assumption I was making in my data. I think it might be tricky to do what you're looking to do without the ability to establish that 1-1 relationship between your two fact tables.
You were right. Actually I managed to find a common value between the two tables (the initial invoice number) and now the visuals are working. But unfortunately it`s not returning the expected results, and don`t know for sure why. Now I believe that the formula and logic that I am using it is not the right one.
To provide more details about the data:
1. In my Sales table I have all the shipped orders / customer / month (each order has assigned only one invoice number)
2. In my corrections table, I have all the corrections done by line (product), meaning that if we corrected 1 invoice, but 20 products from it, I will have the invoice reference no. from above duplicated for 20 times.
3. Manually we are calculating billing accuracy % as (total number of invoices - number of invoices which we "touched") / total number of invoices
4. Using the invoice number from point 1 which is the same with invoice reference number from point 2 as the common link between the table, still has kind of a gap, because not all shipped orders will find a reference in the corrections table
5. Not sure how to distinctly count the reference invoice column and the invoice number column for each month, and for each customer to use it in the formula from point 3.
Not sure if this is going to solve your issue, but it looks like you have a 1-Many relationship established between Corrections and Sales. From what you said (corrections could have many records duplicated on Bill Doc, because it has one record per line item correct), it seems like this should be flipped around. 1-Many going from Sales to Corrections, as Invoice Ref # is unique in Sales.