Forum Discussion
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 overlap ratio?
My store is the basis on which I compare the other store visits, so my store has to be 100%.
| Store name | Date | customer count | customer in total | My store total | calculation [customer total] / [my store total] | overlap ratio % |
| My store | Jan 2020 | 1 | 2 | 2 | 2/2 | 100% |
| A | Jan 2020 | 1 | 1 | 2 | 1/2 | 50% |
| B | Jan 2020 | 1 | 1 | 2 | 1/2 | 50% |
| My store | Jan 2020 | 1 | 2 | 2 | 2/2 | 100% |
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,
KellyDid I answer your question? Mark my post as a solution!
3 Replies
v-kelly-msftCommunity Support
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,
KellyDid I answer your question? Mark my post as a solution!- amitchandak
Super User
Anonymous , Not clear, are you looking for
https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390
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.
store customer count overlap ratio My store 5 100% A store 1 20% B store 2 40%
