Forum Discussion
Calculating Lift % on an average base (that doesn't change when selecting a week)
Hi Power BI Community,
I am looking for a little insight. My Non-Promo $/Str/Week calculates based on a column filter "No". The same goes for the Promo $/Str/Week calculation. So, I edit interactions on the bar graph to the non-promo so it doesn't change to blank (red boxes). The Lift % formula when selected a promo week on the bar changes to infinity as the week shows a promotion value and "0" for non-promo.
Current formulas:
- Non-Promo $/Str/Wk = iferror(([TY Non-Promo Dollars]/[TY Store Selling])/[Non-Promo Weeks],0)
- TY Non-Promo Dollars = calculate(sum('LOWES Dol Sales'[Sales ($)]),filter('LOWES Dol Sales','LOWES Dol Sales'[Promotion]="no"))
- TY Store Selling = if([TY Dollars]>0,DISTINCTCOUNT('LOWES Dol Sales'[Location ID]),0)
- Weeks Selling = DISTINCTCOUNT('LOWES Dol Sales'[Week ID])
- Dollar % Lift = (([Promo $/Str/Wk]-[Non-Promo $/Str/Wk])/[Non-Promo $/Str/Wk])
How can I make the formula be on a dynamic promo number as week is selected vs. the non-promo number being used (based on overall average - $115 for this item, but will change for others) vs. "0"? Should I be using an Average measure? Any thoughts/help is always appreciated.
Regards,
MT
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
13 Replies
- vanessafvg
Community Champion
what is the outcome you looking for? what result do you expect to see on the visual its not clear instead of infinity. Of course this might have nothing to do with it but if you use the divide() function you can set the 0 values to an alternative result but I am not 100% clear on what you are asking for as an outcome.
https://docs.microsoft.com/en-us/dax/best-practices/dax-divide-function-operator
- vanessafvg
its hard to see without actually having data, but I can tell you what i think you need to do. Otherwise if this doesn' hit the mark can you share some data?
but essentially you need to remove the filter that you have selected that blanks out this measure
Dollar % Lift = calculate(divide(([Promo $/Str/Wk]-[Non-Promo $/Str/Wk]),[Non-Promo $/Str/Wk]),
removefilter('tablename'[whichevercolumsyoufilteredon])
Hi Vanessa,
Thank you for your help. Unfortunately it did not work - the Dollar % Lift came up as blank. I tried to remove the column which was labeled "promotion." I can share data if you like. I am unsure on how to do that.Let me know what works for you. Thank you once again for your patience and help.
- vanessafvg
if you have dropbox or onedrive you can share from there.
Hi Vanessa,
Thank you so much. It was a success for the Dollar Lift, and I used it for the units and it worked as well. I guess now, I'm hoping that the "price change" could be done using that template you sent. It did not work our based on using "Var calc"
I am guessing because it only requires subtraction vs. something more complicated with the calculate / remove filter, it basically made the card what the promo price is when it's a promo week vs. when it's not, using the promo price difference. Any chance you have an idea?
Thanks in advance.
- vanessafvg
where are you using price change? please point me to this in the dashboard
Hi Vanessa,
It's on the same tab - I am just using the promo price minus the average non promo price.
I tried using the var calc in your formula setting it up similarly, but to no avail. I tried using filters/remove filters/average/averagex/divide on the price difference. I even tried adjusting the calculations for the average promo retail/non-promo retail. it was in the 11th post. let me know if this helps.