Forum Discussion
Summarize (Pivot) Table
Hi Anonymous,
Sorry, I haven't described my scenario clearly.
The formula I shared in my second reply should be useful when you created the relationship with Link colunm for the two tables.
If you don't have relationship, you should use this formula below.
Column = CALCULATE ( SUM ( Table2[FTE Proportion] ), FILTER ( 'Table2','Table2'[Link]=EARLIER(Table1[Link] ) ) )
Just wondering if there are values in 'Table1', but no matching value in 'Table2' if it will return a value of zero, or if it will cause a problem?
For your question, I think this expample should explain this scenario. In Table 1, we have different links but in Table 2 we only have one matched link. For the result column, we could see if there is no matched values in table 2, it will show blank in Table1.
I also made a simple example which should make you clear.
Best Regards,
Cherry
Anonymous It will be great if you can post some sample data (copiable format) and expec. But anyway based on your post, I can suggest you to create an intermediate calculated table (With sum of values of a field you are interested in with group by Area Code 2 and GL Account) from your main lookup table. Now this intermediate table will be your lookup table which already has summed up values for each combination of AreaCode 2 + GL Account. Hope this helps !!
Thank you for the help.
Hi Anonymous,
What about this measure?
Measure = CALCULATE ( SUM ( Table2[value] ), FILTER ( 'Table2', 'Table2'[Table] = RELATED ( Table1[Table 1] ) ) )If you still need help, please share your data sample as table format so that I can make a copy and have test with your data sample.
In addition, I would appreciate it if you could share your desired output.
Best Regards,
Cherry
Hi Cherry,
Thanks for your help. I tried your DAX expression, but it returns the same number for every row.
I can't see how I can attach a file for you to test the data on. I have an excel version that I can attach if that's possible. I've copied and pasted below, maybe that will work. When I use the DAX expression you gave me I get the result in column "FTE".
My tables are related through the colulmn "link". What I'm trying to do is for every row that has a unique value (ie. Link = 201611001), sum up all the values in the "FTE Proportion" column of the second table, and put them in a new column in the first table. Hope that makes sense. In excel a sumif formula would do this for me.
COLL2 COLL3 FFS Level 3 GL Account Link Total Amount Allocation 1 Allocation 2 FTE SUMIF 21000 20161 3011 1001 201611001 882501.76 661876.32 220625.44 3.136730309 4.98 21000 20161 3013 1701 201611701 169228.68 159632.38 9596.3 3.136730309 0.65 21000 20161 3014 1101 201611101 138464.76 131989.62 6475.14 3.136730309 0.08 21000 20179 3011 1001 201791001 13032089.39 8743944.57 4288144.82 3.136730309 67.42 21000 20179 3013 1701 201791701 18569913.68 9159696.04 9410217.64 3.136730309 114.2 21000 20179 3014 1101 201791101 2606951.97 2251952.71 354999.26 3.136730309 53.51 21000 21100 3011 1001 211001001 221992.36 166494.27 55498.09 3.136730309 3.03 21000 21100 3013 1701 211001701 757491.66 757491.66 0 3.136730309 0.08 22000 20176 3011 1001 201761001 7734973.49 6282546.329 1452427.161 3.136730309 52.58 22000 20176 3013 1701 201761701 4647075.43 3353017.66 1294057.77 3.136730309 26.57 22000 20176 3014 1101 201761101 2704472.89 2634420.35 70052.54 3.136730309 30.22 22000 20180 3011 1001 201801001 5619435.23 4720325.593 899109.6368 3.136730309 46.97 22000 20180 3013 1701 201801701 2437414.14 2251792.75 185621.39 3.136730309 7.28 22000 20180 3014 1101 201801101 4412830.23 4399555.8 13274.43 3.136730309 24.35 22000 22100 3011 1001 221001001 47143.97 39600.9348 7543.0352 3.136730309 0 22000 22100 3013 1701 221001701 526079.55 526079.55 0 3.136730309 1.9 22000 22100 3014 1101 221001101 229.99 229.99 0 3.136730309 0 22000 22300 3014 1101 223001101 24.84 24.84 0 3.136730309 0 COLL2 COLL3 GL Cost Centre GL Project Code GL Natural Account FTE Proportion LINK 21000 20161 594 2032 1001 0.18 201611001 21000 20161 594 2032 1001 0.41 201611001 21000 20161 594 2032 1001 0.15 201611001 21000 20161 594 2032 1001 0.06 201611001 21000 20161 594 2032 1001 0.42 201611001 21000 20161 594 2032 1001 0.16 201611001 21000 20161 594 2032 1001 0.16 201611001 21000 20161 594 2032 1001 0.03 201611001 21000 20161 594 2032 1001 0.03 201611001 21000 20161 594 2032 1001 0.08 201611001 21000 20161 594 2032 1001 0.02 201611001 21000 20161 594 2032 1001 0.04 201611001 21000 20161 594 2032 1001 0.03 201611001 21000 20161 594 2032 1001 0.11 201611001 21000 20161 594 2032 1001 0.09 201611001 21000 20161 594 2032 1001 0.41 201611001 21000 20161 594 2032 1001 0.03 201611001 21000 20161 594 2032 1001 0.07 201611001 Hi Anonymous,
Please create the calculated column with this formula below.
Column = CALCULATE ( SUM ( Table2[FTE Proportion] ), FILTER ( 'Table1','Table1'[Link]=EARLIER(Table1[Link] ) ) )Then you could get the output like below.
The 2.48 is the sum of FTE Proportion which the link is 20161101 in the sceond table.
Best Regards,
Cherry