Create a calcualtion based on a column fro another table

[James_Ma]

Hi ,

I am struggling to build calculation that calculates 2 different values depending on which value is in another column from another table.

The example table shows % CAT INA and % MT INA and an invoice Gross total. The idea is ot have an additional column to work out the % of the gross total, depending on which category (from the case table) the case is.

current xlxs file used for data with Category column from the case table and Invoice from the invocie table

 In an ideal world I would like to see the table content as shown in the below (excel) table.

Name	Category	Count of CaseID	Sum of %age CAT	Hourly CAT	Sum of %age MT	Hourly MT	Invoice Total	Expected result
Manager 1	CAT	9	40%	£37.50	35%	£35.00	£102,048.90	£40,819.56	CAT % * Invoice total
Manager 1	MT	81	40%	£37.50	35%	£35.00	£598,368.04	£209,428.81	MT % * Invoice total
Manager 2	CAT	40	45%	£41.39	45%	£42.28	£610,521.62	£274,734.73	CAT % * Invoice total
Manager 2	MT	55	45%	£41.39	45%	£42.28	£691,696.36	£311,263.36	MT % * Invoice total
Manager 3	CAT	20	40%	£40.00	40%	£40.00	£382,477.08	£152,990.83	CAT % * Invoice total
Manager 3	MT	58	40%	£40.00	40%	£40.00	£621,870.37	£248,748.15	MT % * Invoice total
Manager 4	CAT	10	7%	£42.28	7%	£42.28	£772,153.77	£54,050.76	CAT % * Invoice total
Manager 4	MT	3	7%	£42.28	7%	£42.28	£10,715.69	£750.10	MT % * Invoice total
Manager 5	CAT	3	42%	£45.00	40%	£45.00	£69,779.26	£29,307.29	CAT % * Invoice total
Manager 5	MT	22	42%	£45.00	40%	£45.00	£190,326.63	£76,130.65	MT % * Invoice total
Manager 6	CAT	33	55%	£60.00	50%	£50.00	£662,207.63	£364,214.20	CAT % * Invoice total
Manager 6	MT	75	55%	£60.00	50%	£50.00	£703,763.96	£351,881.98	MT % * Invoice total
Manager 7	CAT	34	50%	£48.60	45%	£37.49	£652,758.78	£326,379.39	CAT % * Invoice total
Manager 7	MT	37	50%	£48.60	45%	£37.49	£323,699.96	£145,664.98	MT % * Invoice total
Manager 8	CAT	29	50%	£47.00	45%	£57.00	£562,362.24	£281,181.12	CAT % * Invoice total
Manager 8	MT	40	50%	£47.00	45%	£57.00	£363,363.61	£163,513.62	MT % * Invoice total
Manager 9	CAT	98	55%	£53.00	50%	£45.00	£2,450,060.27	£1,347,533.15	CAT % * Invoice total
Manager 9	MT	75	55%	£53.00	50%	£45.00	£885,702.17	£442,851.09	MT % * Invoice total
Manager 10	CAT	57	52%	£53.00	47%	£43.00	£1,343,946.74	£698,852.30	CAT % * Invoice total
Manager 10	MT	82	52%	£53.00	47%	£43.00	£802,398.41	£377,127.25	MT % * Invoice total

[speedramps · Accepted solution]

Try this

Answer = 
VAR mycategory = SELECTEDVALUE(yourtable[Category])
RETURN
SWITCH(
    mycategory,
"CAT",SUM(yourtable[Sum of %age CAT]) * SUM(yourtable[Invoice Total]),
"MT",SUM(yourtable[Sum of %age MT]) * SUM(yourtable[Invoice Total])
)

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.