Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
5 REPLIES 5
v-rzhou-msft
Community Support
Community Support

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. 

dedelman_clng
Community Champion
Community Champion

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
Not applicable

@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. 

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] )
    )
)

 

2020-08-25 10_53_50-scratch3 - Power BI Desktop.png

 

(H/T to @MFelix for the suggestion)

 

Hope this helps

David

Hi @Anonymous - I don't think you can use DAX or PowerQuery/M to create rows in your data unless it involves pivoting/unpivoting model data.  Those calculations would probably be better off being done in the data model.

 

David

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.