Forum Discussion

Chris1300's avatar
Chris1300
Helper II
4 years ago

Data changing when adding legend

I have a column chart that shows running total of distinct Login by week date.   I then add a legend to the same chart but the distinct count CHANGES (see total values above column). I am unable to...
Need Help
lbendlin's avatar
lbendlin
Super User
4 years ago

your running total calculation is not correct. You need to move the MAX() part outside of the CALCULATE() part (by using a variable). Pseudo code: 

Running Total = 
var m = MAX([Date])
return calculate(distinctcount([NewLogin]),[Date]<=m)

 

Chris1300's avatar
Chris1300
Helper II
4 years ago

Did not work. the result is the same even with MAX() outside the calculate or inside.

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Please provide sanitized sample data that fully covers your issue.

    • Chris1300's avatar
      Chris1300
      Helper II
      4 years ago

      I have given an example dataset shown below.

       

      My goal is to make 2 charts. The first Chart would be a column chart. X-axis would have the [week_num_date] and the Y-axis would have the Cumulative Total (aka Running Total of distinct login), which I created a measure. 

       

       

      Running Total = 
CALCULATE (
    DISTINCTCOUNT('Table2'[NewLogin]),
    'Table2'[Week_Num_Date]<= MAX('Table2'[Week_Num_Date]))

       

       

      The next chart would also be a similar column chart, but to show a legend of [EMP_DEPT_NM]. When I add this legend the overall distinct count changes and unable to show similar values as Chart 1. 

       

       

       

       

       

       

       

       

      NewLoginEMP_DEPT_NMWeek_Num_Date
      B4714Branch Operations5/22/21
      B7788Branch Operations5/22/21
      C3553Lending5/22/21
      C1254Lending5/22/21
      A9948President/CEO''s Office5/29/21
      B3683President/CEO''s Office5/29/21
      C3553Lending5/29/21
      C2222Finance6/5/21
      C1296Finance6/5/21
      B6326Information Services6/5/21
      A9865Branch Operations6/5/21
      B7788Branch Operations6/5/21

       

      • wesleygeddes555's avatar
        wesleygeddes555
        New Member
        3 years ago

        I have been experiencing the exact same issue. 

         

        Here is the measure: 

        Number of Issues during the Period = 
VAR EndDatePerVisual = MAX('Date'[Date])
VAR StartDatePerVisual = MIN('Date'[Date])
VAR RESULT = 
    CALCULATE(
        [Number of Issues],
        REMOVEFILTERS('Date'),
        Issues[Start Date] <= EndDatePerVisual,
        Issues[Due Date] > StartDatePerVisual || ISBLANK(Issues[Due Date])
    )
RETURN RESULT

        It's really easy to visualize this problem when using a "Line and clustered column chart:

        The configuration of this visual is here: 

         

        The measure being stored in "Column y-axis" is the SAME measure in the "Line y-axis." But yet the total value for the line and the columns are different.