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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you for the help. 

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

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