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

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's avatar
v-piga-msft
Resident Rockstar
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

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

    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's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi Anonymous,

     

    You're welcome. It's glad that we can help.:smileyvery-happy:

     

    Best  Regards,

    Cherry