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

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

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

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