Forum Discussion
New Customer Calculation
Hi All, I'm probably doing something dumb, but I cannot figure out how to make this formula work properly. This is supposed to ignore any slicer/filter on the ProfitCenterGroup Table, but when I click the slicer, it doesn't seem to ignore it. In addition, it calculates way too low, even if it were considering the filter setting. Perhaps this is something in the relationships??
Absolute New Customers =
COUNTROWS (
FILTER (
ADDCOLUMNS (
VALUES ( PLS_Analytics[End_Customer_Key] ),
"PreviousSales",
CALCULATE (
COUNTROWS ( PLS_Analytics ),
ALL ( 'ProfitCenterGroup Table' ),
FILTER (
ALL ( 'RA_Daily_Calendar' ),
'RA_Daily_Calendar'[Date] < MIN ( 'RA_Daily_Calendar'[Date] )
)
)
),
ISBLANK ( [PreviousSales] )
)
)
Here's an image of the relevant tables and relationships.
Help is appreciated. Thanks in advance!
- Anonymous8 years ago
HI Shelley,
Perhaps you can try to use below measure if it works on your side.
Absolute New Customers = COUNTROWS ( FILTER ( SUMMARIZE ( PLS_Analytics, PLS_Analytics[End_Customer_Key], "PreviousSales", CALCULATE ( COUNTROWS ( PLS_Analytics ), ALL ( 'ProfitCenterGroup Table' ), FILTER ( ALL ( 'RA_Daily_Calendar' ), 'RA_Daily_Calendar'[Date] < MIN ( 'RA_Daily_Calendar'[Date] ) ) ) ), ISBLANK ( [PreviousSales] ) ) )Regards,
Xiaoxin Sheng
2 Replies
- AnonymousNot applicable
HI Shelley,
Perhaps you can try to use below measure if it works on your side.
Absolute New Customers = COUNTROWS ( FILTER ( SUMMARIZE ( PLS_Analytics, PLS_Analytics[End_Customer_Key], "PreviousSales", CALCULATE ( COUNTROWS ( PLS_Analytics ), ALL ( 'ProfitCenterGroup Table' ), FILTER ( ALL ( 'RA_Daily_Calendar' ), 'RA_Daily_Calendar'[Date] < MIN ( 'RA_Daily_Calendar'[Date] ) ) ) ), ISBLANK ( [PreviousSales] ) ) )Regards,
Xiaoxin Sheng
- AnonymousNot applicable
It works for me. Thank you. In my case study it required a little change . I needed to change below line
MIN ( 'RA_Daily_Calendar'[Date] )
and use a calculate measure to display results for each month (without any change it displaied repeated total counted value).