Calculating the sales lift based on temperature using DAX

carlosxxss — Fri, 21 Apr 2023 12:17:06 GMT

I want to calculate the lift in the sales numbers if the outside temperature increases. For example if it is 30°C outside you would sell more icecream than when it is 10°C outside. I want to do this by first creating the average of base sales under 20°C and then divide the average sales that occure on days above 20°C with this base. This gives me a lift factor (ex. 1.3 when it is 25°C meaning i would sell 30% more than compared with my base).

I have my sales and temperature data in 1 table (see pbi file below). I now have created 2 extra tables in Power Query, one containing sales data on days below 20°C and one containing sales data on days equal and above 20°C. In DAX in calculate the sales average of both tables and divide them by eachother. I put the data in a matrix and add the temperature (bins) to the columns. I want to see the uplift per weekday so i also added the weekdays in the rows. This gives me the matrix below:

But when adding more and more data my Power BI file gets bigger and slower and the two extra tables i created are not helping. I want to calculate the matrix shown above with only using 1 total sales table and DAX measures. But when i try this and add the data to a matrix i only get the total uplift, not per °C bin because it's shows 'infinity'.

Can you please help me to created the above matrix by only using DAX and the 'Sales_Total' table (and not the two extra tables i created) in the attached PBI file below?

Power BI file (onedrive link): Power BI Example.pbix

Re: Calculating the sales lift based on temperature using DAX

lbendlin — Wed, 26 Apr 2023 01:23:04 GMT

See attached

Re: Calculating the sales lift based on temperature using DAX

carlosxxss — Fri, 28 Apr 2023 06:55:32 GMT

Thank you that worked! For those wondering what is was without opening the attachment:

I divided both averages with the '/' operator, which created the 'infinity' value. What @Ibendlin did was creating 2 measures:

Average below 20 = CALCULATE(AVERAGE(Sales_Total[Sales]),REMOVEFILTERS(Sales_Total[temperature_2m_max °C (bins)]),Sales_Total[temperature_2m_max °C]<20) Lift = DIVIDE(AVERAGE(Sales_Total[Sales),[Average below 20])

And then put the output of the Lift measure in the matrix

topic Calculating the sales lift based on temperature using DAX in DAX Commands and Tips

Calculating the sales lift based on temperature using DAX

Re: Calculating the sales lift based on temperature using DAX

Re: Calculating the sales lift based on temperature using DAX