cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
evgeniam Frequent Visitor
Frequent Visitor

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))
 
table.png
 
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!!
 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Incorrect DAX Measure Totals in Table Visual

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.

 

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

2 REPLIES 2
Super User
Super User

Re: Incorrect DAX Measure Totals in Table Visual

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.

 

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

evgeniam Frequent Visitor
Frequent Visitor

Re: Incorrect DAX Measure Totals in Table Visual

@ImkeF ,

 

This works beautifully. Thank you!!!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)