Forum Discussion
Calculate Minimum Across Multiple Tables by Row
Hello, so I have 4 tables that are related by a primary reference number. In each of the 3 tables, there is a column X, Y, and Z. What I would like to do is create a new table that lists the primary references, the minimum value of X across the 4 tables (for that row's primary reference), and do the same for Y and Z. I've been looking around and I can't seem to find a formula or anything that would achieve this.
For instance, if we had:
Table1
Prim.Ref. X Y Z
1 2.1 3.3 4.2
2 5.0 4.4 1.3
Table2
Prim.Ref. X Y Z
1 2.2 4.3 4.1
2 5.5 3.5 1.6
Table3
Prim.Ref. X Y Z
1 2.0 3.5 5.3
2 5.6 3.7 2.6
Then I'd like to create a table
MinTable
Prim.Ref. mX mY mZ
1 2.0 3.3 4.1
2 5.0 3.5 1.3
Hi GSynchro ,
Try this 3 measures:
mX = Calculate(Minx(Union('Table 1','Table 2','Table 3'), [X]))mY = Calculate(Minx(Union('Table 1','Table 2','Table 3'), [y]))mZ = Calculate(Minx(Union('Table 1','Table 2','Table 3'), [Z]))My output based on your sample data
Let me know fi this works
Thanks,
Tejaswi
3 Replies
Hi GSynchro ,
Try this 3 measures:
mX = Calculate(Minx(Union('Table 1','Table 2','Table 3'), [X]))mY = Calculate(Minx(Union('Table 1','Table 2','Table 3'), [y]))mZ = Calculate(Minx(Union('Table 1','Table 2','Table 3'), [Z]))My output based on your sample data
Let me know fi this works
Thanks,
Tejaswi
Yes! This worked perfectly!!! Thank you so much
Hi,
I suggest you try this approach:
- Append data from all 3 Tables into a 4th Table
- Select the first column of the 4th Table, right click and select "Unpivot other columns"
- To your visual, drag the first column to the row labels, second to the column labels and write this measure
=MIN(Data[Value])
Hope this helps.
