Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Created A Netted Column and Relationship

I have the two tables below and in table 1 I am trying to create a netted column so that I can match it up against table 2. If you look at table 2's labor values, they all add up to table 1's regular pay minus transfer to capital: regular value. I want to create a new value in table 1's financial account column that has "Netted: Regular Pay" that takes those two values and adds them together so it is in line with table 2. How can I go about doing that?

 

Table 1     
RA NumberFinancial AccountPeriodFiscal YearType  Value 
530Regular Pay122020Actual $  34,316.83
530Overtime122020Actual 
530Transfer to Capital: Regular122020Actual $      (116.51)
530Transfer to Capital: Overtime122020Actual 
530Vehicle122020Actual 
530Material122020Actual $       509.00
530Other122020Actual 
530Outside Services122020Actual $  15,820.00

 

Table 2      
RA NumberFinancial AccountPeriodFiscal YearType  Value Short Fin Account
530LABOR_ALL122020Actual $       244.21Regular Pay
530LABOR_ALL122020Actual $       131.48Regular Pay
530LABOR_ALL122020Actual $    1,700.96Regular Pay
530LABOR_ALL122020Actual $       295.99Regular Pay
530LABOR_ALL122020Actual $  14,619.67Regular Pay
530LABOR_ALL122020Actual $  (6,812.80)Regular Pay
530LABOR_ALL122020Actual $       197.22Regular Pay
530LABOR_ALL122020Actual $    6,812.80Regular Pay
530LABOR_ALL122020Actual $       837.40Regular Pay
530LABOR_ALL122020Actual $       683.92Regular Pay
530LABOR_ALL122020Actual $  15,489.47Regular Pay
530OUT_SERV122020Actual $       400.00Outside Services
530OUT_SERV122020Actual $    7,000.00Outside Services
530OUT_SERV122020Actual $    7,000.00Outside Services
530OUT_SERV122020Actual $    1,420.00Outside Services
530MATERIALS122020Actual $       275.00Material
530MATERIALS122020Actual $          70.00Material
530MATERIALS122020Actual $          39.00Material
530MATERIALS122020Actual $       125.00Material
Need Help

5 Replies

  • dedelman_clng's avatar
    dedelman_clng
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous  - 

     

    It is a bit unclear what you are trying to accomplish.  Are these "tables" actual data tables, or are they visualizations that you want to show based on the data model?  Please share a pbix with sensitive data scrubbed out if you can.  Also see here for more tips: How to Get Your Question Answered Quickly 

     

    Thanks

    David

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      dedelman_clng, they are two entirely seperate tables. Unfortuneatly, I am not able to share through my workspace, as I see no way to upload the file here and the websites to upload a file to have been blocked. 

      • dedelman_clng's avatar
        dedelman_clng
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi Anonymous -

         

        I can give you some code for this very specific case (it will specifically work for all RAs, Periods, Fiscal Years and Types to calculate "Netted: Regular Pay"), so you may have to expand it if you needs this same calculation done for other variables.

         

        It involves create a table that is Tab1 merged with a calculated table:

         

        Tab1_Netted =
UNION (
    SUMMARIZE (  //Summarizing Tab1 to get the columns in the right order for UNION
        Tab1,
        Tab1[RA Number],
        Tab1[Period],
        Tab1[Fiscal Year],
        Tab1[Type ],
        Tab1[Financial Account],
        Tab1[ Value ]
    ),
    SUMMARIZE (
        FILTER ( Tab2, Tab2[Financial Account] = "LABOR_ALL" ),
        Tab2[RA Number],
        Tab2[Period],
        Tab2[Fiscal Year],
        Tab2[Type ],
        "Financial Account", "Netted: Regular Pay",
        "Value", SUM ( Tab2[Value] )
    )
)

         

         

        (H/T to MFelix for the suggestion)

         

        Hope this helps

        David

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

    Could you tell me if your problem has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem.

    I think 34,316.83 -116.51= Sumx(filter(table,Short Fin Account = "Regular Pay"),value), but I don't know what does the netted column looks like and what is the netted column's calculated logic.

    Could you show me the result you want? And this may make it easier for me to understand.

    Or share me with your pbix file from your Onedrive for Business.

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.