Summarisation of two tables and linkage within script environment

[Vito]

I am in a situation whereby I had to create to actual table:

CCSumm2022 =

var CC2021 = SUMMARIZE(CCDatabase,CCDatabase[COST_ELEMENT_NAME], CCDatabase[Key], "Cost 2021",sum(CCDatabase[Value]))

var CC2022v = SUMMARIZE(CC2022,CC2022[Cost element name],CC2022[Key],"Cost 2022", sum(CC2022[Value]))

return CC2022v and return CC2021

then I had to 

add a column to CC2022v via:

Cost 2021 = LOOKUPVALUE(CCSumm2021[Cost 2021],CCSumm2021[Key],CCSumm2022[Key])

Final result was:

As I have to do the same with other databases that has 2022 and 2021 records, I was trying something like

CCSumm2022 =

var CC2021 = SUMMARIZE(CCDatabase,CCDatabase[COST_ELEMENT_NAME], CCDatabase[Key], "Cost 2021", sum(CCDatabase[Value]))

var CC2022v = SUMMARIZE(CC2022,CC2022[Cost element name],CC2022[Key],"Cost 2022", sum(CC2022[Value]))

return ADDCOLUMNS(CC2022v, "Cost 2021", LOOKUPVALUE(CCSumm2021[Cost 2021],CCSumm2021[Key],CCSumm2022[Key]))

In doing so I was trying to create the two summarised table as variables, lookup the 2021 value by using the key and add it to CC2022v table used to produce the chart.

It did not work. Is there a way to add a new column on a variable from another variable on the fly?

Thank you for helping

[v-shex-msft]

Hi @Vito,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng