Forum Discussion
Multiply Table by a Reference Table With Conditions
Hello! I'm looking for a solution to multiply values in a main table with ones in a reference table. My problem is very similar to this solved solution: Solved: Help with Multiply columns by reference table - Microsoft Fabric Community
However, I have one wrinkle that I can't seem to get around.
The tables have, for lack of a better term, two keys.
Basically, every location also has a condition. What I want to do is multiply the reference value against the location that has the same condition
DESIRED VALUE = Location with same condition multiplied by the corresponding value in the reference table.
I've joined the tables by the location column, but can't seem to write a measure that does what I need. Below is where I get stuck. I tried unpivoting the columns but that creates a many to many relationship due to multiple locations in a reference table, and I still get stuck on how to write the measure.
Reference Table Attempt
Measure =
SUMX (
'Main Table';
'Main Table'[Value] * RELATED ( 'Reference Table'[???] )
)
Reference Table Unpivoted Attempt
Unpivot Measure =
SUMX (
'Main Table';
'Main Table'[Value] * RELATED ( 'Reference Table'[Value] (? WHERE Attribute = 'Main Table'[Condition] ?)
)
Below is sample data. Note in the live data there are hundreds of conditions:
Main Table:
| Location | Condition | Value |
| Uptown | A | 10 |
| Uptown | A | 20 |
| Uptown | B | 30 |
| Downtown | A | 40 |
| Downtown | B | 50 |
| Downtown | B | 60 |
Reference Table (A and B are the same conditions as the above table):
| Location | A | B |
| Uptown | .5 | .6 |
| Downtown | .4 | .7 |
Reference Table Unpviot A & B
| Location | Attribute | Value |
Uptown | A | .5 |
| Uptown | B | .6 |
| Downtown | A | .4 |
| Downtown | B | .7 |
Expected result:
| Location | A | B |
| Uptown | 35 (70 * .5) | 18 (30 * .6) |
| Downtown | 16 (40 * .4) | 77 (110 * .7) |
Hey Anonymous ,
this is my measure:
Measure = sumx( 'Main Table' , var currentLocation = [Location] var currentCondition = [Condition ] return 'Main Table'[Value] * CALCULATE( SUM( 'Reference Table Unpviot A & B'[Value] ) , 'Reference Table Unpviot A & B'[Location] = currentLocation , 'Reference Table Unpviot A & B'[Attribute] = currentCondition ) )This is a simple Matrix visual (build by the columns of the Main table):
Please check the expected result for Location Uptown and Condition A, it seems the provided data does not match with the expected result.
You can not create a measure that reacts on different columns, at least not if you want the solution fully dynamic, independent of the "number" of conditions, for this reason I use the unpivoted table, but do not try to create any relationship.
Please be aware that in my solution the Main Table and the "Reference Table Unpivot ..." are not related, this are the only two table of the model:
Hopefully, this provides what you are looking for.Regards,
Tom
2 Replies
Hey Anonymous ,
this is my measure:
Measure = sumx( 'Main Table' , var currentLocation = [Location] var currentCondition = [Condition ] return 'Main Table'[Value] * CALCULATE( SUM( 'Reference Table Unpviot A & B'[Value] ) , 'Reference Table Unpviot A & B'[Location] = currentLocation , 'Reference Table Unpviot A & B'[Attribute] = currentCondition ) )This is a simple Matrix visual (build by the columns of the Main table):
Please check the expected result for Location Uptown and Condition A, it seems the provided data does not match with the expected result.
You can not create a measure that reacts on different columns, at least not if you want the solution fully dynamic, independent of the "number" of conditions, for this reason I use the unpivoted table, but do not try to create any relationship.
Please be aware that in my solution the Main Table and the "Reference Table Unpivot ..." are not related, this are the only two table of the model:
Hopefully, this provides what you are looking for.Regards,
Tom
That got it TomMartens ! Thanks an absolute ton!