I'm sure I'm missing something simple, but I cannot figure this out.
I have three tables linked together via a link table. Two of the tables have data in them that I need for a formula. One table contains ticket transactions with hours per ticket. The other table, linked by Cust ID thru a Link table, contains the Customer and the $ value per hour. I want to take the hours on each ticket (record) and multiply them by the $ value per the type of savings column in the customer table.
How do I do this?
I tried to add this column in the ticket table:
Improved HP ($) =
'Ticket'[Improved HP (Hrs) column] *
LOOKUPVALUE('ROI Table'[HP $ Value Multiplier],'ROI Table'[Cust ID], 'Ticket'[Ship-To-ID])
Which worked at first, but I actually have three different categories, each with a different $ value column by customer for which I need the same calculation. As I mentioned, the first one worked, but when I tried to create the others, I received a circular reference error and now even the first one doesn't work.
I then tried to go to the Customer table, which should contain one record per customer and then tried this:
calculated column =
'ROI Table'[HP $ Value Multiplier] * CALCULATE ( SUM ( 'Ticket'[Improved HP (Hrs)] ),
ALLEXCEPT ( 'Ticket', Ticket[Ship-To-ID], Ticket[Category ID]) )
But I don't know what this is doing. It comes up with a high odd looking number, like perhaps it's across the entire table, for all rows in the visual.
Any ideas?
Thanks.
