Forum Discussion

MSuser5's avatar
MSuser5
Icon for Helper III rankHelper III
3 years ago
Solved

Find Customer count those not received amount

Hi All,   I have raw data which is contains customers monthly amount, In this i want to find how many customers received amount first quarter(Q1) and in those customers who haven't get amount in se...
  • danextian's avatar
    3 years ago

    Hi MSuser5 ,

     

    First off, when you post a sample, please post one that be easily copy-pasted (not an image). The community wants to help but make it easy for us to help you by not making us manually type the data. I had to use an OCR to avoid most of the manual stuff.

    Going back, your data is not in the best format for reporting. I''d unpivot everything except Customer column in Power Qurery before creating measures. Change the data type accordingly after unpivoting. Load the data and then create the measures.  See below:

    Customers with amount received in Q1 = 
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Customer] ),
        FILTER ( FILTER ( 'Table', QUARTER ( 'Table'[Date] ) = 1 ), 'Table'[Value] > 0 )
    )
    
    Customers with amount received in but not in Q2 = 
    VAR q1 =
        //summary of customers that received in Q1
        SUMMARIZE (
            FILTER ( 'Table', NOT ( ISBLANK ( [Customers with amount received in Q1] ) ) ),
            'Table'[Customer]
        ) //summary of customers that received in Q2
    VAR q2 =
        SUMMARIZE (
            FILTER ( FILTER ( 'Table', 'Table'[Value] > 0 ), QUARTER ( 'Table'[Date] ) = 2 ),
            'Table'[Customer]
        )
    RETURN
        //removes the customer that are in q1 based on the summary in q2 and then counts the remaining ones
        COUNTROWS (
            EXCEPT ( q1, q2 )
        )
    

     

    Please see sample pbix.