Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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

BobBI's avatar
BobBI
Resolver III
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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.