Forum Discussion

mpavlofsky's avatar
mpavlofsky
New Member
5 years ago

Perfect Invoice measure?

Hi all - I'm looking to create a "perfect invoice" measure in Power BI but I don't know the necessary DAX. Can someone help me? 

 

Right now, I have a measure computing total number of invoices by running a DISTINCTCOUNT on the sales order reference numbers in my fact sales table. 

 

From there, a "perfect invoice" is an invoice with no lines shorted or credited. I attempted to compute this using a CALCULATE function: 

 

CALCULATE(

   [Invoices], 

   fact_sales[shortquantity] = 0, 

   fact_sales[returnquantity] =0

)

 

However, this measure only removed the imperfect lines from the resulting table for calculation, and therefore counted any invoice with at least 1 perfect invoice line as entirely perfect. 

 

Can anyone help me develop the correct version of this measure? 

Need Help

3 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    5 years ago

    Hi mpavlofsky ,

     

    Can you provide a small sample of your fact table data please?

    I'm pretty sure the solution will be to use FILTER and && conditions, but need to understand your data format.

     

    *EDIT*  Try this:

    _noofPerfectInvoices =
CALCULATE(
  [Invoices],
  FILTER(
    fact_sales,
    fact_sales[shortquantity] = 0
    && fact_sales[returnquantity] = 0
  )
)

     

    Thanks,

     

    Pete

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    Hi mpavlofsky,

     

    Could you provide sample data and expected output after removing sensitive data?
    Sample data and expected output would help tremendously.
    Please see this post regarding How to Get Your Question Answered Quickly:
    https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

     

    Best Regards,
    Link

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    Hi mpavlofsky

     

    Is your issue solved?

    If the issue has been solved, please adopt the solution to help others

    If you still have some question, please don't hesitate to let me known.‌‌

     

    Best Regards,

    Link

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!