Running Total in the Tooltip

[wiltonizaquiel]

Hey guys,

I'm facing a trouble to calculate the Running Total's measure in the Tooltip page.

I've create following measure:

RT = 
CALCULATE(
           SUM ( f_Mov[qtd] ),
           FILTER ( ALLSELECT (d_Calendar), d_Calendar[Date] <= MAX (d_Calendar[Date] )
)

It works fine in the main page:

My data:

Date	Qtd	RT
jul-21	144	144
ago-21	800	944
set-21	500	1.444

In the next chart, the bars are the Qtd, meanwhile the dotted line is the RT measure:

I'd like to create a Tooltip that should show the RT.

So I've create a new page and select only the RT measure.

 My issue is when I linked the Tooltip with the chart, it shows the wrong result.

For example, when I hover the mouse above ago/21 month it shows 800, when should show 944.

Any tips what to do to solve this problem?

Thanks a lot.

[Anonymous · Accepted solution]

Hi @wiltonizaquiel 

I have a test in your sample. I think the incorrect result from the same meausre [RT] in your report page tooltip may be caused by filter and relationship. Here I suggest you to create an unrelated Month_Year Table to create a slicer and update your measures.

Unrelated Month Year = SUMMARIZE(d_Calendar,d_Calendar[Month_Year],d_Calendar[Ordem])

Measures:

SUM_QTD = 
CALCULATE (
    SUM ( f_Mov[Qtde] ),
    FILTER (
        d_Calendar,
        d_Calendar[Month_Year] IN VALUES ( 'Unrelated Month Year'[Month_Year] )
    )
)

RT = 
CALCULATE (
    SUM ( f_Mov[Qtde] ),
    FILTER (
        ALL ( d_Calendar ),
        AND (
            d_Calendar[Month_Year] IN VALUES ( 'Unrelated Month Year'[Month_Year] ),
            d_Calendar[Date] <= MAX ( d_Calendar[Date] )
        )
    )
)

Result is as below.

Best Regards,
Rico Zhou

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

[Anonymous · Accepted solution]

Hi @wiltonizaquiel 

I have a test in your sample. I think the incorrect result from the same meausre [RT] in your report page tooltip may be caused by filter and relationship. Here I suggest you to create an unrelated Month_Year Table to create a slicer and update your measures.

Unrelated Month Year = SUMMARIZE(d_Calendar,d_Calendar[Month_Year],d_Calendar[Ordem])

Measures:

SUM_QTD = 
CALCULATE (
    SUM ( f_Mov[Qtde] ),
    FILTER (
        d_Calendar,
        d_Calendar[Month_Year] IN VALUES ( 'Unrelated Month Year'[Month_Year] )
    )
)

RT = 
CALCULATE (
    SUM ( f_Mov[Qtde] ),
    FILTER (
        ALL ( d_Calendar ),
        AND (
            d_Calendar[Month_Year] IN VALUES ( 'Unrelated Month Year'[Month_Year] ),
            d_Calendar[Date] <= MAX ( d_Calendar[Date] )
        )
    )
)

Result is as below.

Best Regards,
Rico Zhou

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

[wiltonizaquiel · Accepted solution]

Hi @Anonymous

Thank you so much for you help!

Thanks to your help I was able to find a solution to my problem.

The only things I've changed in your model was the measures and add a column with the last date of the month to your Unrelated Month Year :

I renamed your Unrelate Month Year table to AUX_CALENDAR.

AUX_CALENDAR =
SUMMARIZE(
          d_Calendar,
          d_Calendar[Month_Year],"LAST_DATE",LASTDATE(d_Calendar[Date])
)

SUM_QTD =
CALCULATE(
          SUM(f_Mov[Qtde]),
          FILTER(
                 d_Calendar,
                 d_Calendar[Month_Year] in VALUES (AUX_CALENDAR[Month_Year])
           )
)

RT =
SWITCH(
      TRUE(),
      SELECTEDVALUE (AUX_CALENDAR[Month_Year]) in VALUES (d_Calendar[Month_Year]),
      CALCULATE ( [SUM_QTD],
                 FILTER(
                        ALL(AUX_CALENDAR),
                        AUX_CALENDAR[LAST_DATE] <= MAX (d_Calendar[Date])
                        )
                  ),
      BLANK()
)

And I made a relationship between the AUX_CALENDAR [Month_Year] and d_Calendar[Month_Year].

 It works perfectly fine for me.

Below the pbix.

RT_Tooltip.pbix 

[TheoC]

Hi @wiltonizaquiel 

I just put the following quickly to show you.  Your measure, returns this:

In the below screenshot, all I did was switch your measure to the Tooltip field of the visual and drag Qtd directly into the Column values field, returning the below: 

I believe the only difference between your measure and mine is that I used the Dates per the existing Table date rather than using the Date column from the Date table.  Therefore, I believe that the cause may be arising from the X Axis of your visual - which is the Dates you're using on your X Axis?  Otherwise, it may be a relationship issue between both tables?

Thanks heaps and hope the above helps.

Theo

[wiltonizaquiel]

Hi, @TheoC 

Thanks for your answer.

The measure works fine when I do what you suggest (Moved the RT measure to Tooltip field), but I'd like to  create a Tooltip because I would put more measure in it not just the RT measure, and unfortunately its not work 😥...

I'm attaching the pbix.

RT_Tooltip_Pbix 

Thanks a lot!

[Anonymous]

Hi @wiltonizaquiel 

I create a sample to have a test. I suggest you to use ALL function instead of ALLSELECTED in your measure.

My Sample is as below.

f_Mov:

d_calendar:

d_calendar =
ADDCOLUMNS (
    CALENDAR ( MIN ( f_Mov[Date] ), EOMONTH ( MAX ( f_Mov[Date] ), 0 ) ),
    "Mont-Year", FORMAT ( [Date], "MMM-YY" ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
)

Measure:

RT = 
CALCULATE(
           SUM ( f_Mov[qtd] ),
           FILTER ( ALL(d_Calendar), d_Calendar[Date] <= MAX (d_Calendar[Date] )
))

Tooltip:

Result is as below.

Best Regards,
Rico Zhou

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

[wiltonizaquiel]

Hi, @Anonymous 

Thanks for your answer!

Unfortunately it's not work... I need when the user filter the date, it shows the Running Total's only the date selected.

So if the user selected the Aug-21 and Sep-21, it should show:

Month-Year	Qtd	RT
Aug-21	800	800
Sep-21	500	1300

And it's showing 944 and 1444 for the Aug-21 and Sep-21, respectively... 😕

Do you have another tips?

Thanks!

[TheoC]

Hi @wiltonizaquiel 

I just put the following quickly to show you.  Your measure, returns this:

In the below screenshot, all I did was switch your measure to the Tooltip field of the visual and drag Qtd directly into the Column values field, returning the below: 

I believe the only difference between your measure and mine is that I used the Dates per the existing Table date rather than using the Date column from the Date table.  Therefore, I believe that the cause may be arising from the X Axis of your visual - which is the Dates you're using on your X Axis?  Otherwise, it may be a relationship issue between both tables?

Thanks heaps and hope the above helps.

Theo

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[wiltonizaquiel]

Hi @Ashish_Mathur!

Of course! See if it works:

RT_Tooltip_pbix 

Thanks a lot! 

[Ashish_Mathur]

Hi,

I don't think a tool tip grabs the context of the time dimension which you have dragged to the X-axis of your graph.   Anyways, i think you should edit your RT measure to:

RT = CALCULATE([SUM_QTD],DATESBETWEEN(d_Calendar[Date],MINx(allselected(d_Calendar),d_Calendar[Date]),MAX(d_Calendar[Date])))

[TheoC]

Hi @wiltonizaquiel 

I just put the following quickly to show you.  Your measure, returns this:

In the below screenshot, all I did was switch your measure to the Tooltip field of the visual and drag Qtd directly into the Column values field, returning the below: 

I believe the only difference between your measure and mine is that I used the Dates per the existing Table date rather than using the Date column from the Date table.  Therefore, I believe that the cause may be arising from the X Axis of your visual - which is the Dates you're using on your X Axis?  Otherwise, it may be a relationship issue between both tables?

Thanks heaps and hope the above helps.

Theo