Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

calculate Overlap ratio

Hi, can anyone help me here?  I got store name, date, customer count and I calculated customer in total for each place. How can I get the [customer total] / [my store total)  in order to get the ov...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi @nabe ，

     

    With the columns "store name","Date","customer count",you can calculate out the columns:"customer in total","my store in total"&"overlap ratio",for the detais,you need 3 calculated columns as below:

     

    Customer in total = CALCULATE(SUM('Table'[customer count]),ALLEXCEPT('Table','Table'[Store name]))
    My store in total = CALCULATE(SUM('Table'[customer count]),FILTER('Table','Table'[Store name]="My store"))
    Overlap ratio = FORMAT(DIVIDE('Table'[Customer in total],'Table'[My store in total]),"percent")

    And you will see:

     

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    amitchandak 

    thanks for your reply.

    I'm not looking for the % of subtotal.

    I want the % of the overlap between "my store" and other stores.

    5 visitors to "my store" (100% ), within these 100% (or 5 people) , 1 visitor went to store A (1/5 20%), 2 visitors went to store B (2/5 40%).

     

    *The customer count is calculated using the customer ID, so I know that they are the same customer.

     

    storecustomer countoverlap ratio
    My store5100%
    A store120%
    B store240%