Create new column to fetch value of matching column names against it

[Anonymous]

I have a table in excel that looks like:

Companyname total project cost  total project revenue    Gs care Revenue    Gs care cost    MN cost %

companya             2400                        5200                          560                            240                26.6

companyb             4100                        6200                          780                            280                27.9

I want in power bi to look like:

Companyname  Scope    total project cost  total project revenue    Gs care Revenue    Gs care cost    MN cost %

companya            TP                2400                        5200                          

companya            gs                                                                            560                           240

companya            mn                                                                                                                                26.6 

So basically im adding a new column scope and if value is tp it should fetch the values of columns having total poject in it.

Help!!

[v-yingjl]

Hi @Anonymous ,

Just use unpivot in power query and create a calculated column [Scope], use a Matrix visual to show it:

See a sample file in the below that hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yingjl ,

Thanks it is working. But need help to create a double header.

Right now output is:

                        gs care cost     gs care revenue      mncost %      total Project cost

Companya            

gs                     240                    560

mn                                                                            26

tp                                                                                                     2400

can i create a header like :

GS header for gs care cost  and gs care revenue a

cost header for mncost %    and total Project cost

                                          GS                                             cost

                        gs care cost     gs care revenue      mncost %      total Project cost

Companya            

gs                     240                    560

mn                                                                            26

tp                                                                                                     2400

Let me know how it can be done

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Based on the sample file, create another calculated column which is simliar with the previous column:

Header = 
SWITCH(
    [Attribute],
    "gs care cost","GS",
    "gs care revenue", "GS",
    "Cost"
)

Atttached the modified in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yingjl ,

i have checked all the columns are same as you have did,But Double header is not coming.

let me know what i"m missing.

power Bi file - Link 

Thanks!

[v-yingjl]

Hi @Anonymous ,

In the Drill on option, choose 'columns' and click 'expand all down one level in the hierarchy' to show the double header.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous - Well, this is quite an odd one, but if you had that extra column somewhere, you could do the following:

New Table =
  ADDCOLUMNS(
    GENERATE(
      SELECTCOLUMNS('Table',"Companyname",[Companyname]),
      SELECTCOLUMNS('ScopeTable',"Scope",[Scope])
    ),
    "total project cost",IF([Scope]="TP",MAXX(FILTER('Table',[Companyname]=EARLIER([Companyname]),[total project cost]),
    "total project revenue",IF([Scope]="TP",MAXX(FILTER('Table',[Companyname]=EARLIER([Companyname]),[total project revenue]),
...
  )

However, I really don't think you want to do that honestly. I would instead unpivot your value columns and then add a column to your table:

Scope = SWITCH([Attribute],
    "total project cost","TP",
    "total project revenue","TP",
    ...
   )

[Anonymous]

@Greg_Deckler ,

the add columns is giving error.Screenshot -Link 

error is :unexpected parameter ',[Total Project ASBL Revenue k EUR ]'.

[Greg_Deckler]

Access error on link

[amitchandak]

@Anonymous , what is formula or logic for TP, GS. If they measure you can use show on row .

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/