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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
kirbynguyen
Helper II
Helper II

Report Page Tooltip displaying the wrong value

Hello, I am creating a scatterplot using X and Y values and then I calculate the predicted value through linear regression in DAX and then calculate the residual between the predicted value and the Y value. The values are all correct. Here is a sample of the data:

kirbynguyen_1-1667486650231.png

 

Whenever I add the X value and the residual values to a scatterplot, they display correctly as such:

kirbynguyen_3-1667486921231.png

 

The issue arises when I created a report page tooltip and tried to display the X and the residual value. All of the values are displayed as 0, which is incorrect:

kirbynguyen_2-1667486899927.png

 

If I take off the report page tooltips and use the default tooltip, the correct values are displayed. How do I get the report page tooltips to display the correct values instead of 0?

 

Here is my predicted value code and the Residual is just [Y Value] - [Predicted Value]:

Predicted Value =
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( 'Dates'[Date] ),
            "Known[X]",  [X Value] ,
            "Known[Y]",  [Y Value] 
        ),
        AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) )
        && NOT( ISBLANK( SUM(Data[Value])) )
    )
VAR Count_Items =
    COUNTROWS ( Known )
VAR Sum_X =
    SUMX ( Known, Known[X] )
VAR Sum_X2 =
    SUMX ( Known, Known[X] * Known[X] )
VAR Sum_Y =
    SUMX ( Known, Known[Y] )
VAR Sum_XY =
    SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
    AVERAGEX ( Known, Known[X] )
VAR Average_Y =
    AVERAGEX ( Known, Known[Y] )
VAR Slope =
    DIVIDE (
        Count_Items * Sum_XY - Sum_X * Sum_Y,
        Count_Items * Sum_X2 - Sum_X * Sum_X
    )
VAR Intercept = Average_Y - Slope * Average_X
RETURN 
    Intercept + Slope * [X Value]

 

3 REPLIES 3
v-yiruan-msft
Community Support
Community Support

Hi @kirbynguyen ,

Is Residual a measure or a calculated column? Please try to create a measure Residual as below and check if you can get the correct result...

Measure = SUM([Y Value]) - [Predicted Value]

 

If the above one is not your expected result, please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yiruan-msft 

After some more testing, I found that the Predicted Value always calculates as the Y value when used in the tooltip and I'm assuming that it isn't taking into account the prediction value as a whole.

For ex:

kirbynguyen_0-1668107496999.png

looking at the data in the table, if you were to filter it to just one day, the predicted value will be exactly the same as the Y Value and thus, the residual would be 0. (for 10/3/2022, the Y Value is 83.63 and the Predicted Value is 87.13, but if filtered to one point, the Predicted Value changes to the Y Value 83.63, so the new [Y - Predicted Value] is 0 instead of -3.5)

 

So I found out why this was happening, but still do not know how to resolve this issue. Any help would be appreciated.

Everything is a measure. The residual needs to be a measure because the time frame may change and that changes the slope of the line. What's weird to me is that the X value shows the correct value, but the residual does not

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.