Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
I have a problem with a straight table and not sure if I am going in the right direction.
I would like a straight table that shows the main costs and the additional costs for that company ignoring any other additional costs from other companies
I have 2 tables one with the main purchase and another table with additional purchases
looks a bit like this
Flight ID | Company No. | Net Cost |
123456 | AMERICA | 5000 |
Flight ID | Buy From No | Add Costs | Sent to Accounts |
123456 | FLL | 1,000.28 | Yes |
123456 | AMERICA | 5,065.12 | Yes |
123456 | AMERICA | 465.3 | Yes |
123456 | REP/GODFREY | 830 | Yes |
What I would like to see in a straight table is
Flight ID | Company No. | Net Costs | Additional Cost |
123456 | AMERICA | 5000 | 6,065.40 |
This just shows the costs and additional costs of the company.
But I get
Flight ID | Company No. | Net Costs | Additional Cost |
123456 | AMERICA | 5000 | 7,360.70 |
Which is the total of the all the other companies and not just the main company.
For this one I did this
(SUM({$<[Sent to Accounts]={'1'}>}[Additional Cost]))
Then I tried adding some set analysis / variable (I don't realty understand them all that well, so I am completely doing it wrong)
(SUM({$<[Operator No.]={$(=only(Operator No.))},[Sent to Accounts]={'1'}>}[Additional Cost]))
I just get 0 as a result.
I hope this is clear enough, please get in touch if you need any more information
Thank you for your time
Mark
I guess you would need to sum up only if the Sent to Accounts is Yes.
if so the expression would be:
sum({<SentToAccounts={Yes}>}if(CompanyNo=BuyFromNo,AddCosts))
You have the same ID for all the companies. So when you try to get a straight table including Id, it sums up all the values in Additional costs.
How did you get 6,065.40 here? Which additional costs are you summing up?
Hello Reshma,
Thank you for your response. That cost is made up of the two "AMERICA" records in the Buy From No
123456 | AMERICA | 5,065.12 | Yes |
123456 | AMERICA | 465.3 | Yes |
I want to ignore the two lines
123456 | FLL | 1,000.28 | Yes |
and
123456 | REP/GODFREY | 830 | Yes |
My aim is to do a cost analysis on our suppliers as some times the costs are hidden in taxes and extras. let say there is 1 'enquiry' and it costs 1,000. This 1,000 may be made up of 400, 300, 200 and 100. Only the 400 and 100 are from our main supplier the other 200 and 300 are from sub suppliers. I would like to ignore the 200 and 300 value and see the 400 as the main cost and 100 as the extra.
I hope that makes more sense.
Thanks again!
Mark
Sorry I seem to have got my figures incorrect. the total should have been 5530.42 for the additional cost. The two records from 'AMERICA'
set analysis can also exclude values if you use -= :
Sum({<[Company No]-={"FLL","REP/GODFREY"} >} [Net Costs])
Please see the attached solution. Thanks
Hi that seemed to work, The last part is to only sum by 'Sent to accounts'. As some times the line is outstanding or cancelled.
a bit like this
SUM(IF({$<[Operator No.]=[Buy From No],[Sent to Accounts]={'1'}>}[Net Amount]))
I know this works for totalling up all the lines
(SUM({$<[Sent to Accounts]={'1'}>}[Additional Cost]))
Thanks!
Mark
I guess you would need to sum up only if the Sent to Accounts is Yes.
if so the expression would be:
sum({<SentToAccounts={Yes}>}if(CompanyNo=BuyFromNo,AddCosts))
Hi Reshma,
That seems to do the trick. Thank you for your time, you have been a great help.
Thanks
Mark
Thank you for this information it is useful