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 Cherry,

 

Thanks for your help. I tried your DAX expression, but it returns the same number for every row. 

 

I can't see how I can attach a file for you to test the data on. I have an excel version that I can attach if that's possible. I've copied and pasted below, maybe that will work. When I use the DAX expression you gave me I get the result in column "FTE". 

 

My tables are related through the colulmn "link". What I'm trying to do is for every row that has a unique value (ie. Link = 201611001), sum up all the values in the "FTE Proportion" column of the second table, and put them in a new column in the first table. Hope that makes sense. In excel a sumif formula would do this for me. 

 

COLL2COLL3FFS Level 3GL AccountLinkTotal AmountAllocation 1Allocation 2FTESUMIF
210002016130111001201611001882501.76661876.32220625.443.1367303094.98
210002016130131701201611701169228.68159632.389596.33.1367303090.65
210002016130141101201611101138464.76131989.626475.143.1367303090.08
21000201793011100120179100113032089.398743944.574288144.823.13673030967.42
21000201793013170120179170118569913.689159696.049410217.643.136730309114.2
2100020179301411012017911012606951.972251952.71354999.263.13673030953.51
210002110030111001211001001221992.36166494.2755498.093.1367303093.03
210002110030131701211001701757491.66757491.6603.1367303090.08
2200020176301110012017610017734973.496282546.3291452427.1613.13673030952.58
2200020176301317012017617014647075.433353017.661294057.773.13673030926.57
2200020176301411012017611012704472.892634420.3570052.543.13673030930.22
2200020180301110012018010015619435.234720325.593899109.63683.13673030946.97
2200020180301317012018017012437414.142251792.75185621.393.1367303097.28
2200020180301411012018011014412830.234399555.813274.433.13673030924.35
22000221003011100122100100147143.9739600.93487543.03523.1367303090
220002210030131701221001701526079.55526079.5503.1367303091.9
220002210030141101221001101229.99229.9903.1367303090
22000223003014110122300110124.8424.8403.1367303090

 

 

 

COLL2COLL3GL Cost CentreGL Project CodeGL Natural AccountFTE ProportionLINK
2100020161594203210010.18201611001
2100020161594203210010.41201611001
2100020161594203210010.15201611001
2100020161594203210010.06201611001
2100020161594203210010.42201611001
2100020161594203210010.16201611001
2100020161594203210010.16201611001
2100020161594203210010.03201611001
2100020161594203210010.03201611001
2100020161594203210010.08201611001
2100020161594203210010.02201611001
2100020161594203210010.04201611001
2100020161594203210010.03201611001
2100020161594203210010.11201611001
2100020161594203210010.09201611001
2100020161594203210010.41201611001
2100020161594203210010.03201611001
2100020161594203210010.07201611001
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

  • 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?