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

[Anonymous]

Hello,

Has anyone had this problem before - I have a measure in my report and i used it in 2 visuals:

1. Card
2. Clustered column chart

However, Im getting 2 different totals in the 2 visuals. I used another visual -  Waterfall chart - to check the total.

Result:

• Card Total CTC - 25, 207,338
• Clustered column Total CTC = 33,112,767
• Waterfall Total CTC = 33,112,767

I am confused. Appreciate your help!

Thanks,

cvld

[jdbuchanan71 · Accepted solution]

Sorry, I realize my error.  My measure should be

TOTAL CTC Months = SUMX ( VALUES ( Date[Month Year] ), [TOTAL CTC] )

[Anonymous]

@jdbuchanan71 it worked perfectly!! thank you so much!

[jdbuchanan71]

@Anonymous 

If you add another measure like this:

TOTAL CTC Months = SUMX ( Date[Month Year], [TOTAL CTC] )

Does that give you the correct value when placed in a card?  It should be forcing the the months to be considered even when they are not shown in the visual.  I'm not saying this is ideal but if it works then you know the month context is what is causing the [TOTAL CTC] measure to give an invalid amount in the card.

[Anonymous]

Hello @jdbuchanan71 I tried your formula but it is only accepting the Date table not a column. Did you mean like this 

TOTAL CTC Months = SUMX ( Date, [TOTAL CTC] )

This is returning 125,339,016.26 though.

[jdbuchanan71]

No, It would be whatever column you have in your date table that is the month-year.  Basically it will replicate the context of the bar chart inside the measure then sum the result.

[Anonymous]

hi @jdbuchanan71 i have a Month-year column

Month Year = FORMAT('Date'[Date], "MMMM") & " "& FORMAT('Date'[Date],"YYYY")

But when trying to add it in my measure, i only have these options

Sorry if I'm not following. 

[gtacchini]

Try a simple SUM, you'll see they match.

[Anonymous]

hi @gtacchini  can't use SUM because my TOTAL CTC is a measure 😞 

[gtacchini]

@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]

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]

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]

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

[jdbuchanan71]

Check the list of filters that are being applied to each of the visuals. You can see the list by hovering over the funnel icon on the top right of the visual. It looks like there is an additional filter hitting the card that is not hitting the other 2.

You could also just remake the card and see if that fixes it.

[Anonymous]

Hi @jdbuchanan71 thank you for responding. I dont have filters in any of the visuals. What i do have are page filters. I even cleared out all my page filters but still getting different totals