Forum Discussion

evgeniam's avatar
evgeniam
Frequent Visitor
6 years ago
Solved

Incorrect DAX Measure Totals in Table Visual

Hi Power BI Experts!


I am calculating Revenue per Employee as the sum of revenue time entries in the database. In cases when employees leave the company, we want to discontinue adding their time to the revenue total. 

I used the following measure to account for theat:

 
New Resource Rate*Hours =
var termination_Date = MAX(Employees[TerminationDate])
RETURN
CALCULATE(SUM(RevenueDetail[RateTimesHours]),
FILTER(RevenueDetail, RevenueDetail[Date]<=termination_Date))
 
 
It works, but the total is incorrect. I tried some of the solutions suggested to others dealing with similar issues, but no luck. 
 
Total Rate*Hours =
var termination_Date = MAX(Employee[TerminationDate])
RETURN
SUMX(
VALUES( RevenueDetail[RateTimesHours]),
IF(RevenueDetail[Date]<=termination_Date,
CALCULATE(SUM(RevenueDetail[RateTimesHours]))))
 
This returns an error "A single value for column "Date" cannot be determined..."


I appreciate your help with this!!
 

 

Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi evgeniam 

    I think that something along this line should get you going:

     

    Measure2 =
SUMX (
    FILTER (
        'RevenueDetail',
        'RevenueDetail'[Date] <= CALCULATE ( MAX ( 'Employee'[TerminationDate] ) )
            || ISBLANK ( CALCULATE ( MAX ( 'Employee'[TerminationDate] ) ) )
    ),
    'RevenueDetail'[RateTimesHours]
)

    The MAX(TerminationDate) has to be evaluated on a row-by-row-basis for the Total to calculate correctly.

     

2 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    Hi evgeniam 

    I think that something along this line should get you going:

     

    Measure2 =
SUMX (
    FILTER (
        'RevenueDetail',
        'RevenueDetail'[Date] <= CALCULATE ( MAX ( 'Employee'[TerminationDate] ) )
            || ISBLANK ( CALCULATE ( MAX ( 'Employee'[TerminationDate] ) ) )
    ),
    'RevenueDetail'[RateTimesHours]
)

    The MAX(TerminationDate) has to be evaluated on a row-by-row-basis for the Total to calculate correctly.