Maybe SUMX issue?

DAX DAX Calculations
1

Hi team,

I’ve been going round in circles with an issue and I’m sure it’s to do with SUMX but I don’t know how to resolve it.

I’m trying to create a report that tracks employee’s flexitime balance by week.

I’ve got one measure where the columns don’t add up to the sum of the individual weeks.
I’ve got the following tables
Time = Employee, date, hours booked, task
Calendar = Date, Week No., Year
Employee = Employee, Working hours per week

I’ve got the following measures that seem to work ok and subtotal ok:

  • Hours Booked (this is just the sum of hours submitted)
  • Flex Hours Taken (this is a calculation of Hours booked where the Task = Flexitime)
  • Hours Booked less Flex (Hours booked - Flex Hours Taken)
  • Hours per week (Just the number of contracted hours per week)
  • No of weeks (distinct count of week number)
  • Contracted hours - (Hours per week * No of weeks)

So my issue is with calculating the flex hours gained in a week:
Flex Hours Gained = if((Hours Booked Less Flex - Contracted Hours)<0,0, Hours Booked less Flex-Contracted hours)
This is the measure that has a mismatch between the individual weeks and the column total.

Having read around I believe this can be resolved with SUMX but I’m not sure of the syntax in this particular case.

Can anyone point me in the right direction?

Kind regards

2

Hi @andyb.

To help us further visualize your issue, could you please provide as many as you can of:

  • Your work-in-progress PBIX file, using sanitized data if necessary
  • Your dataset as an Excel file (again, sanitized if necessary)
  • A detailed mock-up (marked-up screenshot or Excel file) of your desired outcome.

Greg

3

Flexi time.pbix (118.0 KB)

Flexitime.xlsm (51.6 KB)

4

Hi @andyb,

For the Total there is no context coming from Weeks, so to recreate that, try this.

Flex hours gained v2 = 
SUMX(
    ADDCOLUMNS( VALUES( Cal2[Year-Week] ),
        "@Value", if(([Hours booked less Flex]-[contracted hours])<0,0,[Hours booked less Flex]-[contracted hours])
    ), [@Value]
)

I hope this is helpful

1 Like
5

Hi @andyb,

I viewed that Hours Booked less Flex had an error: Hours Booked + Flex Hours Taken. I adjusted it and the running total is ok.

Let me know if I answered.

Immagine

6

Hi Melissa,

Thank you for the incredibly quick reply, and I can confirm it worked a treat.

I want to go away and read up functions used in this measure, what key words would I be best searching for - I kind of understand what when wrong but not sure how to express it?

Thanks again for your help

7

Hi Matteo-Italy,

Thanks for your help. The original measure was correct because I made the Flex Hours Taken a minus number already.

I really appreciate you taking the time to help.

Kind regards

8

Hi @andyb,

A good place to start would be with these:

https://portal.enterprisedna.co/courses/305959/lectures/13664651

http://portal.enterprisedna.co/courses/108877/lectures/13350365

https://portal.enterprisedna.co/courses/305959/lectures/4864211

https://portal.enterprisedna.co/courses/305959/lectures/11981612

9

Thank you once again, greatly appreciated.

10

@andyb,

Just to throw one more resource on the stack that @Melissa recommended, I would also suggest taking a look at the DAX Patterns - Fix Incorrect Totals section compiled by eDNA expert @Greg:

In addition to Greg’s excellent overview, and many of the videos Melissa recommended, it also has links to other related forum posts. This DAX Patterns section is a great resource to check when you run into problems, since Greg has developed patterns for the most common issues/questions we see on the forum, and is regularly adding new ones:

image
image1254×653 56.7 KB

I hope this is helpful.

  • Brian
2 Likes
11

That’s very useful thanks, and thanks for taking the time to help me out.

I just wasn’t sure how to articulate what my problem was so didn’t know where to start to research the solution. I’ve used SUMX before to solve simple issues but didn’t know how to use it in this context.

Looks like I’ve got some light reading to do :wink:

Thanks again for taking the time it’s greatly appreciated. I think the money I’ve spent on a subscription to Enterprise DNA is the best money I’ve ever spent in my business , I’ve learnt so much and the response on the forum has been first class.

Kind regards

1 Like