Aggregate of unique monthly visits

[TaylorTako]

Hi Everyone,

I've ran into a bit of a standstill with an issue i'm having on Power Bi..

So basically the large percentage chart on the far right is based on this calculation:

Store Completion Rate = DIVIDE( IF([Stores Call Completed]=BLANK(),0, [Stores Call Completed]) , [Stores Loaded] ) 

This basically is taking unique instances of each store key that appears in the data so for example:

Store A: KEY: 8742 (COMPLETED)

Store B: KEY: 9843 (COMPLETED)

Store C: KEY: 9843 (COMPLETED)

Store 😧 KEY: 4521 (DIDN'T GET COMPLETED)

As you can see store key 9843 pops up twice, this means the data would only count this as 1.. so for the above data it would look like this:

3 Stores loaded

2 Stores completed

Here are the calculations used for both the measures:

Stores Loaded = CALCULATE( [Stores Loaded All],
FILTER(Survey,
'Survey'[Survey Type] <> "Non-Compulsory" &&
'Survey'[Survey Type] <> "Non-Payable" &&
'Survey'[Survey Type] <> "No Store Visit Required"
)
)

Stores Loaded Total = COUNT( 'Survey Response'[StoreKey] )

Stores Call Completed = CALCULATE( [Stores Loaded All]
,FILTER( 'Survey Response', 'Survey Response'[Completed] = TRUE() )
,FILTER(Survey,
'Survey'[Survey Type] <> "Non-Compulsory" &&
'Survey'[Survey Type] <> "Non-Payable" &&
'Survey'[Survey Type] <> "No Store Visit Required"
)
)

This issue is basically this,

If you look at the historical trend, these figures are completely accurate, exactly the percentage I want, this works accurately when looking at just one month. If i filtered on September the large chart on the right would indicate 95% store completion, and this figure is helpful.

However, when I look at multiple months, such as filters on both Sep and Oct, the percentage is 99%...

This is because the formula used in the calculation takes EVERY UNIQUE STORE LOADED, will total this up, and then compare this to whether or not the store was visited JUST ONCE..

It would be better to attain a figure more based on the average store completion percentage across multiple months..

The way i thought I could do this would be adding up stores loaded by month and comparing this to stores completed by month and looking at an aggregate.. to give you some more information on what i mean, i'll write up a hypothetical below:

September:

Visit 1: Store Key '5678' - Completed

Visit 2: Store Key '6765' - NOT COMPLETED

Visit 3: Store Key '3233' - Completed

Visit 4: Store Key '7897' - Completed

Visit 5: Store Key '7897' - Completed

4 Stores were loaded (as we don't count the double up, it's unique stores).

3 Stores were completed (This is despite completing stores 4 times, it looks at unique visits and ticks yes or no based on store)

This would give store completion at 75%.

October:

Visit 1: Store Key '5678' - Completed

Visit 2: Store Key '6765' - NOT COMPLETED

Visit 3: Store Key '1111' - Completed

Visit 4: Store Key '1111' - Completed

Visit 5: Store Key '1111' - Completed

3 Stores were loaded (as we don't count the double ups, it's unique stores).

2 Stores were completed (This is despite completing stores 4 times, it looks at unique visits and ticks yes or no based on store)

This would give store completion at 66%.)

NOW, if we were just looking at a singular month, it would say 75% for Sep or 66% for October.. 

If I looked across both Sep and October what happens is this:

It would look at total visits across the whole period:

Visit 1: Store Key '5678' - Completed

Visit 2: Store Key '6765' - NOT COMPLETED

Visit 3: Store Key '3233' - Completed

Visit 4: Store Key '7897' - Completed

Visit 5: Store Key '7897' - Completed

Visit 1: Store Key '5678' - Completed

Visit 2: Store Key '6765' - NOT COMPLETED

Visit 3: Store Key '1111' - Completed

Visit 4: Store Key '1111' - Completed

Visit 5: Store Key '1111' - Completed

So unique stores is 5

Stores completed is 4

This gives a percentage of 80% across the periods..

I don't want this percentage, i would rather the average across Sep and October.. this could be works out on TOTAL STORES LOADED PER MONTH... example:

4 Stores completed in Sep and 3 in October.. 

So there could be a new measure that adds these up, based on the date range.. so 4 stores in sep and 3 in october, is 7 stores total loaded...

Then the same thing with stores completed which was 3 in sep and 2 in october so that's 5 total stores completed

This would be put into a percentage to 71% and this figure would be better to use...

So basically, is there a way that this is possible... that it adds up the UNIQUE STORE VISITS for that month and has some sort of total for this?

Any assistance is greatly appreciated, apologies for the massive essay! 

Thank you!!

[v-ljerr-msft]

Hi @TaylorTako,

What's the date column in your tables? Do you have an individual Calendar table?

Could you post your table structures with some sample data? It's better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards