Forum Discussion

Shelley's avatar
Shelley
Post Prodigy
8 years ago
Solved

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!

 

 

  • Anonymous's avatar
    Anonymous
    8 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

  • Anonymous's avatar
    Anonymous
    Not 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

    • Anonymous's avatar
      Anonymous
      Not 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).