Forum Discussion
Calculating Lift % on an average base (that doesn't change when selecting a week)
Ok I see what the issue is and its a difficult issue to solve. Because in the bottom graph when you click on the graph and select a day, you are filtering on the low we date and yet you want to preserve low we date filter for the visuals above for the non promo week.
To be honest I can't find a solution that isn't messy to this problem. The only way I can solve this issue is by creating another date table and relinking that and then using the date from the second calendar table on the bottom graph, and remove the filter from this new date table in the dollar % lift measure when you click on it. Not sure if that make sense.
i removed changed the bottom visual to use the calendar 2 table and that solves that problem but its not a clean solution. Also other things to consider if there is no promo value, what should the dollar lift be?There is probably a better solution to this but without understand your business requirements around the promo and non promo dates etc. this is what I got.see if you can download from here?ie
Dollar % Lift =var calc = DIVIDE ([Promo $/Str/Wk]-CALCULATE ([Non-Promo $/Str/Wk],REMOVEFILTERS ( 'Calendar 2'[Low We Date])),CALCULATE ([Non-Promo $/Str/Wk],REMOVEFILTERS ( 'Calendar 2'[Low We Date] )))return calc
Community Championif you have dropbox or onedrive you can share from there.
Hope this works - https://www.dropbox.com/s/y7psihjke0tl8t5/Lowes%202021%20Fiscal%20Year%20-%20Version%204.3.pbix?dl=0
Let me know if there are any issues. Thanks Vanessa.
- vanessafvg
Ok I see what the issue is and its a difficult issue to solve. Because in the bottom graph when you click on the graph and select a day, you are filtering on the low we date and yet you want to preserve low we date filter for the visuals above for the non promo week.
To be honest I can't find a solution that isn't messy to this problem. The only way I can solve this issue is by creating another date table and relinking that and then using the date from the second calendar table on the bottom graph, and remove the filter from this new date table in the dollar % lift measure when you click on it. Not sure if that make sense.
i removed changed the bottom visual to use the calendar 2 table and that solves that problem but its not a clean solution. Also other things to consider if there is no promo value, what should the dollar lift be?There is probably a better solution to this but without understand your business requirements around the promo and non promo dates etc. this is what I got.see if you can download from here?ie
Dollar % Lift =var calc = DIVIDE ([Promo $/Str/Wk]-CALCULATE ([Non-Promo $/Str/Wk],REMOVEFILTERS ( 'Calendar 2'[Low We Date])),CALCULATE ([Non-Promo $/Str/Wk],REMOVEFILTERS ( 'Calendar 2'[Low We Date] )))return calcVanessa - it worked! and the calculations come out! Thank you. Now I can use your info and work on the unit and retail column! Thanks for your help!