Forum Discussion
Summarize (Pivot) Table
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.
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
13 Replies
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 !!
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 ,
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
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.
