Forum Discussion

hatahetahmad's avatar
hatahetahmad
Helper I
8 years ago
Solved

Calculated Invoices Count with filtered Measure

Hello,

 

I have Sales Table contains (Date, ID, Sales Rep, Product, Total)

 

I Counted the Invoices above 20$ (Used calculate and calculated distinct invoices count)

 

But My Question when Invoices Count More than 14 invoices give the number else give me 0

I did If Condition It seems right, but the total giving me the same number as Counted the Invoices above 20$

My tables filterd by Date and Sales Rep Name, I Expect to get the total of invoices that Excedd 20$ per invoice and exceed 14 invoice per working day.

 

 

 

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi hatahetahmad,

     

    Try this new measure.

    Measure =
SUMX (
    VALUES ( data[Date] ),
    IF ( [Invoices Above 20$] >= 14, [Invoices Above 20$], 0 )
)

    aa

     

    Best Regards,

    Dale

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Rather than using an if statement, would it not work if you just use the filter part of the calculate function? 

    Something like:

     

    calculate(distinctcount('Sales Table'[Sales]),'Sales Table'[Invoice] > 14)

    • hatahetahmad's avatar
      hatahetahmad
      Helper I
      8 years ago

      Thank you ThomasDaviesMCR for the reply,

       

      I cant Do that because I made a Mesure that give me invoices Number (each invoice exceed 20$ total)

       

      So, Building in this Measure I want DISTINCTCOUNT of invoices If each day exceed 14 invoices of the 20$

       

      Example: 

      Day 1 - 20 invoices made (only 12 above 20$) then the measure I want to give me 0 for Day 1

      Day 2 - 18 invoices made (only 15 above 20$) then the measure I want to give me 15 for Day 2

      Day 3 - 14 invoices made (only 14 above 20$) then the measure I want to give me 14 for Day 3

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi hatahetahmad,

         

        Try a measure like below. Or you can share a sample.

        Measure =
SUMX ( VALUES ( 'table'[sales] ), COUNT ( 'table'[invoice] ) )

        Best Regards,

        Dale