Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

New Customer Monthly Conversion Rate

Hi all,   I'm currently working on a project related to new customer conversion rates. I would like to calculate and visualize conversion rates for new customer quotes on a monthly basis.   New c...
Need Help
parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Anonymous try changing your measure like below

 

New Customers # Quotes = 
VAR selectedYear = MAX ( 'dim DimDate'[YEAR] )
VAR CustomerTM      = VALUES( 'fact FactSalesDocumentsPPG'[SellToCustomerID] )
VAR PriorCustomers  = CALCULATETABLE( VALUES ( 'fact FactSalesDocumentsPPG'[SellToCustomerID] ), 
                        'dim DimDate'[YEAR] = selectedYear - 3,
                        'dim DimDate'[YEAR] <  selectedYear ,
                        
                        //FILTER( ALLSELECTED(  'dim DimDate' ),
    
                            //'dim DimDate'[DATE] > DATEADD(FIRSTDATE( ( 'dim DimDate'[DATE] )),-3, YEAR ) &&
                            //'dim DimDate'[DATE] < MIN( 'dim DimDate'[DATE] ) ) ,
                        ALL('dim DimProductGroup'),     
                        ALL( 'dim DimSalesperson (External)'), 
                        ALL('dim DimSalesperson (Internal)') ) 

RETURN

    CALCULATE([# Quotes],EXCEPT ( CustomerTM, PriorCustomers ) )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi parry2k ,

     

    Thank you for your reply! Your proposal fixes my problem to some extent. When I'm filtered by customer it is now showing exactly what I want:

     

    However, when I remove that filter the formula does not behave as expected:

    I know that the 433 total is correct. Any idea what could cause the difference?

     

    Thanks in advance.

    • V-lianl-msft's avatar
      V-lianl-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Anonymous,

       

      This may be due to different filtering methods for tables.

       

      Best Regards,

      Liang

      If this post helps, then please consider Accept it as the solution to help the other members find it more qui