Create a calcualtion based on a column fro another table

James_Ma — Thu, 25 Jan 2024 16:43:32 GMT

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.

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

40%

£37.50

35%

£35.00

£102,048.90

£40,819.56

CAT % * Invoice total

Manager 1

40%

£37.50

35%

£35.00

£598,368.04

£209,428.81

MT % * Invoice total

Manager 2

CAT

45%

£41.39

45%

£42.28

£610,521.62

£274,734.73

CAT % * Invoice total

Manager 2

45%

£41.39

45%

£42.28

£691,696.36

£311,263.36

MT % * Invoice total

Manager 3

CAT

40%

£40.00

40%

£40.00

£382,477.08

£152,990.83

CAT % * Invoice total

Manager 3

40%

£40.00

40%

£40.00

£621,870.37

£248,748.15

MT % * Invoice total

Manager 4

CAT

£42.28

£772,153.77

£54,050.76

CAT % * Invoice total

Manager 4

£42.28

£10,715.69

£750.10

MT % * Invoice total

Manager 5

CAT

42%

£45.00

40%

£45.00

£69,779.26

£29,307.29

CAT % * Invoice total

Manager 5

42%

£45.00

40%

£45.00

£190,326.63

£76,130.65

MT % * Invoice total

Manager 6

CAT

55%

£60.00

50%

£50.00

£662,207.63

£364,214.20

CAT % * Invoice total

Manager 6

55%

£60.00

50%

£50.00

£703,763.96

£351,881.98

MT % * Invoice total

Manager 7

CAT

50%

£48.60

45%

£37.49

£652,758.78

£326,379.39

CAT % * Invoice total

Manager 7

50%

£48.60

45%

£37.49

£323,699.96

£145,664.98

MT % * Invoice total

Manager 8

CAT

50%

£47.00

45%

£57.00

£562,362.24

£281,181.12

CAT % * Invoice total

Manager 8

50%

£47.00

45%

£57.00

£363,363.61

£163,513.62

MT % * Invoice total

Manager 9

CAT

55%

£53.00

50%

£45.00

£2,450,060.27

£1,347,533.15

CAT % * Invoice total

Manager 9

55%

£53.00

50%

£45.00

£885,702.17

£442,851.09

MT % * Invoice total

Manager 10

CAT

52%

£53.00

47%

£43.00

£1,343,946.74

£698,852.30

CAT % * Invoice total

Manager 10

52%

£53.00

47%

£43.00

£802,398.41

£377,127.25

MT % * Invoice total

Re: Create a calcualtion based on a column fro another table

speedramps — Thu, 25 Jan 2024 19:20:31 GMT

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.

topic Re: Create a calcualtion based on a column fro another table in DAX Commands and Tips

Create a calcualtion based on a column fro another table

Re: Create a calcualtion based on a column fro another table