Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 Number | Financial Account | Period | Fiscal Year | Type | Value |
530 | Regular Pay | 12 | 2020 | Actual | $ 34,316.83 |
530 | Overtime | 12 | 2020 | Actual | |
530 | Transfer to Capital: Regular | 12 | 2020 | Actual | $ (116.51) |
530 | Transfer to Capital: Overtime | 12 | 2020 | Actual | |
530 | Vehicle | 12 | 2020 | Actual | |
530 | Material | 12 | 2020 | Actual | $ 509.00 |
530 | Other | 12 | 2020 | Actual | |
530 | Outside Services | 12 | 2020 | Actual | $ 15,820.00 |
Table 2 | ||||||
RA Number | Financial Account | Period | Fiscal Year | Type | Value | Short Fin Account |
530 | LABOR_ALL | 12 | 2020 | Actual | $ 244.21 | Regular Pay |
530 | LABOR_ALL | 12 | 2020 | Actual | $ 131.48 | Regular Pay |
530 | LABOR_ALL | 12 | 2020 | Actual | $ 1,700.96 | Regular Pay |
530 | LABOR_ALL | 12 | 2020 | Actual | $ 295.99 | Regular Pay |
530 | LABOR_ALL | 12 | 2020 | Actual | $ 14,619.67 | Regular Pay |
530 | LABOR_ALL | 12 | 2020 | Actual | $ (6,812.80) | Regular Pay |
530 | LABOR_ALL | 12 | 2020 | Actual | $ 197.22 | Regular Pay |
530 | LABOR_ALL | 12 | 2020 | Actual | $ 6,812.80 | Regular Pay |
530 | LABOR_ALL | 12 | 2020 | Actual | $ 837.40 | Regular Pay |
530 | LABOR_ALL | 12 | 2020 | Actual | $ 683.92 | Regular Pay |
530 | LABOR_ALL | 12 | 2020 | Actual | $ 15,489.47 | Regular Pay |
530 | OUT_SERV | 12 | 2020 | Actual | $ 400.00 | Outside Services |
530 | OUT_SERV | 12 | 2020 | Actual | $ 7,000.00 | Outside Services |
530 | OUT_SERV | 12 | 2020 | Actual | $ 7,000.00 | Outside Services |
530 | OUT_SERV | 12 | 2020 | Actual | $ 1,420.00 | Outside Services |
530 | MATERIALS | 12 | 2020 | Actual | $ 275.00 | Material |
530 | MATERIALS | 12 | 2020 | Actual | $ 70.00 | Material |
530 | MATERIALS | 12 | 2020 | Actual | $ 39.00 | Material |
530 | MATERIALS | 12 | 2020 | Actual | $ 125.00 | Material |
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.
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
@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] )
)
)
(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
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |