Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
atoice
Helper I
Helper I

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.

atoice_0-1634752839082.pngatoice_1-1634752882430.png

Current formulas:

  1. Non-Promo $/Str/Wk = iferror(([TY Non-Promo Dollars]/[TY Store Selling])/[Non-Promo Weeks],0)
  2. TY Non-Promo Dollars = calculate(sum('LOWES Dol Sales'[Sales ($)]),filter('LOWES Dol Sales','LOWES Dol Sales'[Promotion]="no"))
  3. TY Store Selling = if([TY Dollars]>0,DISTINCTCOUNT('LOWES Dol Sales'[Location ID]),0)
  4. Weeks Selling = DISTINCTCOUNT('LOWES Dol Sales'[Week ID])
  5. Dollar % Lift = (([Promo $/Str/Wk]-[Non-Promo $/Str/Wk])/[Non-Promo $/Str/Wk])
 
I would think this would be used to fix the Unit % Lift and Price Difference Cards (Blue boxes).
 

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

1 ACCEPTED SOLUTION

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
 
 




If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

13 REPLIES 13
atoice
Helper I
Helper I

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.

where are you using price change?  please  point me to this in the dashboard





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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.

 

 

Promo Price Diff Calc.PNG

Sorry - I didn't add a snapshot.

 

atoice_0-1634907156380.png

So the price difference should be ($18.1). All it is is average promo retail minus average non-promo retail.

vanessafvg
Super User
Super User

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])

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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. 

 

atoice_0-1634821352162.png

 

Let me know what works for you. Thank you once again for your patience and help.

if you have dropbox or onedrive you can share from there.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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.

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
 
 




If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Vanessa - 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! 

vanessafvg
Super User
Super User

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




The expected results for the Dollar Lift in that example would be 115%

 

(247.9 - 115.3)/115.3 = 115% lift in dollars.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.