Forum Discussion

wiltonizaquiel

Frequent Visitor

4 years ago

Solved

Running Total in the Tooltip

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.

Need Help

Anonymous

4 years ago

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.

RT_Tooltip.pbix54 KB

wiltonizaquiel

4 years ago

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

11 Replies

Ashish_Mathur
Super User
4 years ago
Hi,

Share the link from where i can download your PBI file.
- wiltonizaquiel
  Frequent Visitor
  4 years ago
  Hi Ashish_Mathur!
  
  Of course! See if it works:
  RT_Tooltip_pbix
  
  Thanks a lot!
  - Ashish_Mathur
    Super User
    4 years ago
    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])))
Anonymous
Not applicable
4 years ago
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.
RT tooltip.pbix23 KB
- wiltonizaquiel
  Frequent Visitor
  4 years ago
  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
Community Champion
4 years ago
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
  Frequent Visitor
  4 years ago
  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

Not applicable

4 years ago

Hi wiltonizaquiel

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.

RT_Tooltip.pbix54 KB

wiltonizaquiel

Frequent Visitor

4 years ago

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
Community Champion
4 years ago
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
TheoC
Community Champion
4 years ago
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

Forum Discussion

Running Total in the Tooltip

11 Replies

Related content

Running Totals

Running total

Running total as a Tooltip

Waterfall Charts with running totals

Tableau to PowerBI Running Totals Issue

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