Forum Discussion
Max unique values by week by location
Hi all,
I wonder if anyone might be able to steer me in the right direction?
I have a set of data that looks like this, showing if 3 different promotions are available by visit by week by location:
I am trying to calculate the % of promotions that are present across all visits made in each location in that week, but at the moment I do not appear to be work it out.
For example, for location A in week 1 it ought to read 100% of promotion as available, but I am getting 83% instead because it is adding the results of the 2 visits together (5 divided by 6). Likewise in week 2 it should also be 100% as all 3 promotions were available across both of the 2 visits made.
Any ideas?
Thanks
Anonymous
Edited:
1. Place Table1[Location] and Table1[Week] in a table visual
2. Create this measure and place it in the visual (format as % if required):
Measure =
DIVIDE (
MAX ( Table1[Promotion 1] ) + MAX ( Table1[Promotion 2] ) + MAX ( Table1[Promotion 3] ),
3
)
4 Replies
Hi Anonymous
Try this:
1. Place Table1[Location] and Table1[Week] in a table visual
2. Create this measure and place it in the visual (format as % if required):
Measure = DIVIDE ( SUM ( Table1[Total promotions] ), SUM ( Table1[Possible promotions] ) )
Hi AlB,
Thanks for looking, but that is just giving me the percentage total of all promotions across all visits, which would be 83%, which is incorrect - this is becuase as all 3 promotions were active in week 1 in location A at some point. Is there a way to show how many promotions were available by location by week vs total possible promotions?
Thanks!Anonymous
Edited:
1. Place Table1[Location] and Table1[Week] in a table visual
2. Create this measure and place it in the visual (format as % if required):
Measure =
DIVIDE (
MAX ( Table1[Promotion 1] ) + MAX ( Table1[Promotion 2] ) + MAX ( Table1[Promotion 3] ),
3
)AlB thanks for your help, I got it to work using your solution!