cancel
Showing results for
Did you mean:
Highlighted
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 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%
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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:

Best Regards,
Kelly
3 REPLIES 3
Highlighted
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

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper II

## Re: calculate Overlap ratio

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%
Highlighted
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:

Best Regards,
Kelly

Announcements

#### August 2020 Community Challenge: Can You Solve These?

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

#### Community Blog

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

#### 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