Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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