Forum Discussion

negutbm's avatar
negutbm
Frequent Visitor
7 years ago

Billing Accuracy %

Hi guys! Really new into Power BI, and I am struggling a bit to do some KPI calculations.

I have two tables, one with Sales Orders, and one with Corrections. I found out how to calculate the % of accuracy, as (total no. of sales orders - the ones which were corrected ) / total no. of sales orders, simple. The issue appears in the months in which there are countries, or customer for which I haven`t done any correction, and for these ones I cannot make it to show 100%.

 

Apologies if the answer it`s already posted somewhere, but I couldn`t find it.

Added also a printscreen. Thank you!

 

 

 

Need Help

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hard to answer to your question without looking at data and model but easily DISTINCTCOUNT(SalesOrder) is not equal to DISTINCTCOUNT(Invoice Ref #)

    So your formula is (100-5.1) / 100 = 94.9%

    • negutbm's avatar
      negutbm
      Frequent Visitor
      7 years ago

      Hi. Yes, that`s the logic in the formula that I am using. Fields are not the same, but the count resulted from them, if I am doing it manually provides the expected result.

      I`ve also provided more details below on the data and what I am trying to do.

       

      Unfortunately, I`m starting to think that actually it is not possible to do it in the way I want it.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I tried to mock up a simple example based on what you described. Here's the underlying data:

     

    Sales-

     

     

     

     

     

     

     

     

     

    Corrections-

     

     

     

     

     

    I used your same model to compute Billing Accuracy %:

     

    Billing Accuracy = (DISTINCTCOUNT(Sales[Sales Order])-DISTINCTCOUNT(Corrections[Sales Order]))/DISTINCTCOUNT(Sales[Sales Order])
     
    As you can see in the data, I've made it so that for January, Canada doesn't have any corrected orders. I'm still able to get this to show up as 100% on my map, though. Is your underlying data structure different? If not, maybe you just need to tweak the formatting options on your map?
     

    Ben
    • negutbm's avatar
      negutbm
      Frequent Visitor
      7 years ago

      Hi Ben. Thanks a lot! It is very similar with my set of data. Sharing some printscreens from my data to be more specific:

      You Sales table = my ZLIPR F5P table (I`ve highlighted the Sales Order field + the Sold-To Field, as this is the field on which I based the relationship between the two tables). 

      Your Corrections Table = my ZSKU F5P (I`ve highlighted the Invoice Reference No. field + the Sold-To field)

      *I am using the Reference Invoice column to count, as I can have more than 1 correction invoice for each Sales order, but I want to count it corrected just once, while in the other table I am using the Sales order field because here I have 1 sales order = 1 invoice.

      **Even if in both tables there is a Sales Order field, it means smth different, in the Sales Table = the actual order (product sold), in the correction table means the correction order

       

      Now I`m thinking that the Sold-To field, which connects both tables is the issue.

       

      Thank you, Bogdan!

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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.