I have problem where I have a number of workshops with costs from the general ledger and I want to allocate these costs to the type of vehicles that are being serviced at each workshop. I have a matrix where the percentage for each workshop and vehicle is given and that I want to apply to the workshop costs using DAX.
My plan was to convert the matrix into a table looking like below and pull data from that table to create a measure that calculates each vehicle’s share of each workshop, then use that measure in new measures, one for each vehicle. The new measures would calculate the total cost for each vehicle (these measures is then going to be used in other measures).
Workshop Vehicle Share
1102 141 0%
1102 142 10%
1103 141 0%
1103 142 100%
1101 141 80%
1101 142 0%
1105 141 0%
1105 142 50%
1104 141 0%
1104 142 0%
The problem is that both axis in the matrix table relates to the same resource dimension in my data model and since I can only create a relationship with one of the axis in the matrix so the calculations turns out incorrect.
Any brilliant ideas on how to work around this problem?
Brgds,
Caj
My plan was to convert the matrix into a table looking like below and pull data from that table to create a measure that calculates each vehicle’s share of each workshop, then use that measure in new measures, one for each vehicle. The new measures would calculate the total cost for each vehicle (these measures is then going to be used in other measures).
Workshop Vehicle Share
1102 141 0%
1102 142 10%
1103 141 0%
1103 142 100%
1101 141 80%
1101 142 0%
1105 141 0%
1105 142 50%
1104 141 0%
1104 142 0%
The problem is that both axis in the matrix table relates to the same resource dimension in my data model and since I can only create a relationship with one of the axis in the matrix so the calculations turns out incorrect.
Any brilliant ideas on how to work around this problem?
Brgds,
Caj
