Sum up data

[M_SBS_6]

Hi, 

I have this field (surv_quantity) but a job level in my table surv_jobs

I have this field (surv_out) at a sub job level in my table surv_sub_job

What I need to do it take the surv_jobs.surv_quantity - surv_sub_jobs.surv_value

The problem I have is that the value surv_quantity double counts as it see 2 jobs as it has 2 sub jobs. 

Example-

Surv_id=10

surv_quantity I have a total value of 30,000

However, if I look at the detail whereby job 

Surv_id=10

Surv_sub_id=12 value=30000

Surv_sub_id=23 value=30000

By having 30000 twice, it's scewing the numbers as it thinks for that job, the value is 60000 when in actual fact, its 30000. 

Any idea how I can get around this please so it only takes the one value? 

[ryan_mayu]

@M_SBS_6 

then what's the expected output? 12 is 15000 and 23 is 15000?

[M_SBS_6]

Yes, that's right 

[ryan_mayu · Accepted solution]

pls check if this is what you want

[M_SBS_6]

Hi @ryan_mayu yes, essentially that's what I need thanks 

[ryan_mayu]

you are welcome

[grantsamborn]

Hi @M_SBS_6 

This looks like an interesting problem

Is there any way you can provide data that demonstrates the problem with sensitive data removed?  (a pbix would be preferable)

Also, please provide expected results and an explanation of any intermediary steps needed in the calculation.