Forum Discussion
Calculate or get values from a different table
Hi,
What I need in somehow an idea on how to get the following OUTPUT table that you see in the screenshot. The problem is that I have all my data in "ApproveSubmit" table but also I need the column SG% from the other table that contains percentages splits based on each "contract" column. I don't know how to join these two things in one single table.
Also below you can see the tables and relationships that I have at the moment.
In summary what I need is to get the SG % from "Map" table to the other one but without see total amounts like there isn't relationships between them.
Clarification: Contract # in Map table appears many times because is splitted by SG/SG # as you see in the SG Columns marked in yellow. Each Contract has a distribution of SG % that in total sum 100 %. So LookUpValue and/or eliminate duplicates can't be the solution in this case.
And Contract table is for filtering purposes as both tables haven't have unique contracts.
9 Replies
cristianml
Looking at the diagram, the Contract # appears many times in the Approval and the Map table. so you need to tell what other columns we can include along with the Contract # as a unique record from both the tables. This way, you can use the LOOKUPVALUE dax function to bring the SG %hi Fowmy ,
Contract # in Map table appears many times because is splitted by SG and the corresponding % SG # for each contract (Yo can see in the SG Columns and the marked in yellow). So LookUpValue can't be the solution in this case.
From the limited data you've provided, I see no reason to include the "Contract#" table with the single "Contract#" column; I would guess you are using it for filtering. But I bet your "Map" table has a unique column of Contract# as well. You can therefore eliminate the "Contract#" table altogether, and that should fix your numbers.
--Nate
hi Anonymous
Contract # in Map table appears many times because is splitted by SG and the corresponding % SG # for each contract (Yo can see in the SG Columns and the marked in yellow).
Hi cristianml ,
Maybe you should try to merge. Tutorial is here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
I also tried but doesn't work cause the value from ApproveSubmit tab is being duplicated due to have multiple SG % in Map table for each contract. What I need is, with a Slicer by SG over a Pivot table, that can show me the Value from ApproveSubmit but with the correspoding SG % portion.
Example of this :
Hi cristianml ,
1.Merge two tables
2.Expand the table
3.Create two custom columns
4.Remove the unwanted columns and rename the custom columns.
More details you can check the attachment.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
