Summarize (Pivot) Table

[Anonymous]

Hi All,

I'm an excel user trying to model some data in Power BI, but am stuck on the equivalent of a pivot. If I was in Excel, I would pivot the first table below to remove source code, then do a lookup to another table to bring in the sum of another column for each unique value of "Area Code 2" + "GL Account". What's the best way to do this in Power BI? I've tried SUMMARIZECOLUMNS and CALCULATE TABLE but not sure if I'm even on the right track.

If anyone has a good post on how to convert from being an Excel User to a Power BI user that would be super too. 

Thanks in advance. 

This is my base data.I need to use a combination of Area Code 2 & GL Account to bring in the sum of data from another table.

[BobBI]

Hi ,

Not sure what extactly you were trying to achieve , however you can try this and play around little bit to get the desired output.

Your base data is in Table1 ,  Create a calculated table "NewTable" , group by Area code/GL account

Note : you can also create a composite key 'CK' to combine area code 2 and GL to uniquely identify each row.

NewTable=

SUMMARIZE(Table1,
        Table1[Area Code 2],
        Table1[GL Account],
        "CK",Table1[Area Code 2]&Table1[GL Account],
        "Total",CALCULATE(sum(Table1[Total amount]))
        
         )

now if you have to loop value from another table your LookupValue funciton

"lookupValue", LOOKUPVALUE(Table2[total amount],table2[CK],table1[CK])

Hope this gives you a direction.

Good luck ,

SS

[Anonymous]

Hi,

Thank you. The DAX for the NewTable worked, but I'm still not getting the right answer with the lookup. I think I need the equivalent of a SUMIF. In my new table I have a single unique value on each row, but the same value is on multiple rows on the second table and I need the sum of each of those rows. Can you help me with the correct order for the CALCULATE function? Not sure if I have explained this well enough. 

Table 1:

A

B

C

Table 2:

A  1

A  2

A  3

The function should return a new column in Table 1 that has a total of 6 next to A. 

[PattemManohar]

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

[Anonymous]

Thank you for the help. 

[v-piga-msft]

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

[Anonymous]

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

[v-piga-msft]

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

[Anonymous]

Hi Again,

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? 

Regards,

Verity

[v-piga-msft · Accepted solution]

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]

Thank you. I found my problem, my data in my link column wasn't correct for one of the tables. 

The formula works perfectly. 

[v-piga-msft]

Hi @Anonymous,

You're welcome. It's glad that we can help.

Best  Regards,

Cherry

[Anonymous]

Thanks Cherry,

Makes perfect sense but I must have something wrong as I'm still not getting a result. Can you share your dummy file with me so I can try to work through where I've missed something? 

[Anonymous]

Hi Cherry,

Thanks again for your help. 

Can you please explain what the filter is doing? Why does it only refer to Table1? 

I've tried using the formula as written, and also substituting Table2 into the formula but I keep returning blank rows. 

Regards,

Verity