Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Card visual is showing a different value compared to column and waterfall charts

Hello,   Has anyone had this problem before - I have a measure in my report and i used it in 2 visuals: Card Clustered column chart However, Im getting 2 different totals in the 2 visuals. I u...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    Sorry, I realize my error.  My measure should be

    TOTAL CTC Months = SUMX ( VALUES ( Date[Month Year] ), [TOTAL CTC] )
gtacchini's avatar
gtacchini
Icon for Advocate I rankAdvocate I
4 years ago

Anonymous this looks like a filter and row context issue where totals don't necessarily match individual values. I you could please post the measure you are using that would be helpful for debugging. Normally that happens because you're using a measure that should be applied with a row context. Check this video for more info about evaluation contexts.

 

I'll be waiting for your response!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi gtacchini

     

    Now that makes sense. Here are my measures

    Monthly Salary = 
VAR MonthEnd = MAX ( 'Date'[Date] )
VAR MonthStart = MIN ( 'Date'[Date])
VAR StartDate = CALCULATE ( MAX ( Employee[Start_Date__c] ), Employee[Start_Date__c] < MonthEnd )
VAR EndDate =   CALCULATE ( MIN ( Employee[Employment_End_Date__c] ),   Employee[Employment_End_Date__c] > StartDate  )
VAR MonthlySalary =
    CALCULATE (
        SELECTEDVALUE(Employee[Monthly CTC] ),
        Employee[Start_Date__c] = StartDate
    )
VAR DaysEmployed =
    DATEDIFF (
        MAX ( StartDate, MonthStart ),
        IF ( ISBLANK ( EndDate ), MonthEnd, MIN ( EndDate, MonthEnd ) ),
        DAY
    ) +1
VAR DaysInMonth = DAY ( MonthEnd )
RETURN
    IF ( DaysEmployed > 0, MonthlySalary * DaysEmployed / DaysInMonth )
    TOTAL CTC = SUMX( VALUES(Employee[Employee.Id]), [Monthly Salary])

     

    Thanks!

    • gtacchini's avatar
      gtacchini
      Icon for Advocate I rankAdvocate I
      4 years ago

      Yes, thats the trick. You have a semi-additive measure. Totals are not gonna match filtered values. 

       

      Try thinking about this: 

          IF ( DaysEmployed > 0, MonthlySalary * DaysEmployed / DaysInMonth )

      This condition is not being evaluated for each month but for the entire dataset. 

       

      Try reading this and watching the video on my last reply, It'll help you debug and reach a solution. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thank you gtacchini! appreciate your help. I'll definitely check the 2 links you shared