Forum Discussion
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...
Sorry, I realize my error. My measure should be
TOTAL CTC Months = SUMX ( VALUES ( Date[Month Year] ), [TOTAL CTC] )
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
Advocate I
Advocate IYes, 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.
Thank you gtacchini! appreciate your help. I'll definitely check the 2 links you shared