cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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 nameDatecustomer countcustomer in totalMy store total

calculation

[customer total] / [my store total]

overlap ratio %
My storeJan 20201222/2100%
 Jan 20201121/250%
B Jan 20201121/250%
My store Jan 20201222/2100%
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: calculate Overlap ratio

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:

Annotation 2020-04-15 095550.png

 

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: calculate Overlap ratio

@nabe , Not clear, are you looking for

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper II
Helper II

Re: calculate Overlap ratio

@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%
Highlighted
Community Support
Community Support

Re: calculate Overlap ratio

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:

Annotation 2020-04-15 095550.png

 

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Top Solution Authors
Top Kudoed Authors