Forum Discussion
Find Customer count those not received amount
- 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.
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.