Forum Discussion

wiltonizaquiel's avatar
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] ), FIL...
Need Help
  • Anonymous's avatar
    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.

  • wiltonizaquiel's avatar
    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 

TheoC's avatar
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's avatar
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!