Incorrect Measure Total with Measure referencing multiple tables

[rhayward2209]

I'm having the common porblem of not having the total on a table visualistion be a sum of the rows above it. I've looked into solutions for it but I can't seem to find a way to apply them to my more complex measure.

My Measure is as follows:

Not Submitted/Re-Submitted =

IF([Adjusted Billsupp Hours] = 0,0,

(IF(SUM(Payroll[Payroll Hours]) - [BILLED - TWIS] - SUM('NR Hours'[WORKHOURS]) - SUM('Submitted Not Approved'[Total Hrs]) <=0,0,

(SUM(Payroll[Payroll Hours]) - [BILLED - TWIS] - SUM('NR Hours'[WORKHOURS]) - SUM('Submitted Not Approved'[Total Hrs])))))

The value I'm after is 7599.75 not 7546.00

Please let me know if you need more information.

All of the hours are brought in from various tables and are pulled in via a relationship with either the personnel number, Twis Code or Employee name.

Been stuck on this for ages so please let me know if you have any ideas!

[v-janeyg-msft]

Hi, @rhayward2209 

It’s my pleasure to answer for you.

According to your description,Have you tried to use the summarize formula, but still can't get the desired result? If yes, please check the results of the measure in the visual to see if there are any errors in data other than total.

I think it might be other issues such as relationship, time inconsistency, etc.Could you please share some sample data on onedrive for business and your desired result?So we can help you soon.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rhayward2209]

Hi @v-janeyg-msft ,

I would love to share some sample data however the data is sourced from 6-7 different data sets and uses measures so the complexity of providing you that data is quite high.

I agree that the issue probably stems from some kind of relationship issue, or the measure its self as the summarise measure still doesnt give the correct respons.

Please let me know what you think.

[v-janeyg-msft]

Hi, @rhayward2209 

You can try to make a sample with fake data, which only contains a few rows of data.

I’m sorry if you don’t provide more relevant information, it’s hard to help you.

Best Regards

Janey Guo

[rhayward2209]

@amitchandak 

I also watched the youtube video that you linked but I am struggling to figure out how to apply it to a measure that is more complex than the example.

[rhayward2209]

@amitchandak 

Thanks for the reply,

It still doesnt give the right answer. Could it be because the DATE value is not in the same table as the Employee details?

[amitchandak]

@rhayward2209 , my bad , Try like

Not Submitted/Re-Submitted = sumx(summarize( Table, Table[Date], Table[Personned Number], Table[TWIS Code], Table[Name] , "_1" ,
IF([Adjusted Billsupp Hours] = 0,0,
(IF(SUM(Payroll[Payroll Hours]) - [BILLED - TWIS] - SUM('NR Hours'[WORKHOURS]) - SUM('Submitted Not Approved'[Total Hrs]) <=0,0,
(SUM(Payroll[Payroll Hours]) - [BILLED - TWIS] - SUM('NR Hours'[WORKHOURS]) - SUM('Submitted Not Approved'[Total Hrs])))))) ,[_1])

[amitchandak]

@rhayward2209 , Try like

sumx(summarize( Table, Table[Date], Table[Personned Number], Table[TWIS Code], Table[Name] , "_1" ,Not Submitted/Re-Submitted =
IF([Adjusted Billsupp Hours] = 0,0,
(IF(SUM(Payroll[Payroll Hours]) - [BILLED - TWIS] - SUM('NR Hours'[WORKHOURS]) - SUM('Submitted Not Approved'[Total Hrs]) <=0,0,
(SUM(Payroll[Payroll Hours]) - [BILLED - TWIS] - SUM('NR Hours'[WORKHOURS]) - SUM('Submitted Not Approved'[Total Hrs])))))) ,[_1])

Also refer:

https://www.youtube.com/watch?v=ufHOOLdi_jk

[rhayward2209]

@amitchandak 

Thanks for the reply, Im a bit confused due to the lack of an equals sign before SUMX. When i Put in your code it gives me an error saying that SUMX cant work with types boolean. So I may be doing something wrong. Also The dates are pulled from a date table, not the same table as the Name, TWIS code, etc.