Scatter plot displaying odd points outside of the calculated formula

[james_pease]

Hello all, I have been conducting a lot of log regression analysis in excel. Sales are the X value and cost % is the y value. I came up with the following line equation:
y=1.0554-0.06737*ln(x)

I converted this in DAX to graph to be able to calculate a line of expected cost % at a given sales (so our true cost to gage if we are under or over the models cost %).

Here is the calculated column:

Taco Bell Labor Line =

IF(All_Actual_Sales[Actual Sales Null Values]=0,BLANK(),

IF(All_Actual_Sales[Actual Sales Null Values]>10000, (1.0554 - (0.06737* (All_Actual_Sales[LN Actual Sales])))))

Also I created a LN Actual sales column to keep the above forumla more simple:

LN Actual Sales =

IF(All_Actual_Sales[Actual Sales Null Values] > 10000,

LN(All_Actual_Sales[Actual Sales Null Values]))

My question is why are there points that appear off the line?

Keep in mind I could not put the calculated column into the scatter plot without selecting one of the summarization options, so I selected average as it should visually show the trend. If there is a way I can use the formula without having to pick a summarization option that would be ideal.

See the image below:

Im extremely confused becuase the table shows the calculated column is working perfectly:

Does anyone have insight as to when I put the same calculated measure into a scatter plot, I end up with odd points?

Thank you in advance!

[james_pease]

I think I figured it out. Since the scatter plot is the average of the calculated column, it averages out every row for each store (the dots are the stores) then it plots it. Now my follow up question, is there a way I can simply plot that measured column against actual sales? So not having to select a summarization option?

[Anonymous · Accepted solution]

Hi @james_pease ,

You can only put aggregate value or measure in Scatter Chart.

Best Regards,

Jay

[james_pease]

I see gotcha, thank you!