combine 3 measures into 1 column and merge with an existing table

[Lisa_2021]

Hi,  Need help with combine 3 measures into 1 column and merge with an existing table 

I have existing table called table 1.

created 3 measures:
    measure 1, measure 2, measure 3 that corresponds to row 1, 2, 3 in column "Actual"

New table: is an union between table 1 and this new column called "Actual"

Thank you!  Much appreciated.  Lisa

[v-xiaotang]

Hi @Lisa_2021 

One idea, use switch() function to combine the 3 measures into one, but it's hard to say if it will work unless there is more specific data, could you provide some sample data? and what are you trying to calculate? and how shall we assign the measure to the corresponding row?

Best Regards,

Community Support Team _Tang

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

[Lisa_2021]

Thanks Tang.  My company blocks any uploads so I am unable to share data.  I can try to see if I can create something on my personal computer and share the data.  Appreciate you looking into this.

[Anonymous]

From my view, i think this is the structure that you should be figuring and brainstorming about.

Table 1 as a benchmark table. Table 2 as a data table. 

Table 1:

	Min	Max	Target
Grade	A	C	B
Sales	100	150	200
Cost	50	75	150

Table 2:

Sales Person	Sales Order	Grade	Sales	Cost	Measure 1 {Grade}	Measure 2 {Sales}	Measure 3 {Cost}
john	123	A	110	10
Terry	432	A	90	50
Jacob	234	B	140	120
john	542	C	40	40

[Lisa_2021]

Hi.  Unfortunately I can't change the layout of table. 

Grade, Sales, Cost rows are indeed benchmarks on a macro level and we need to compare the actual column to see how we do.

we are not comparing individual customers.  thanks anyway!  Lisa