Dynamic Variable in Calculated Columns

[metaph0r]

Greetings,

I am working on a project that requires a dynamic variable in a calculated column. This calculated column is a composite score, weighted on a handful of determined variables. The dynamic variable in question are the weights themselves; how do I calculate a composite score volumn in PowerQuery with variable weights?

This is an example formula with static weights: 

I want the weights (.3, .6 & .1) to change dynamically based on the report year. I have entered a table for this purpose:

How do I change these weights, based on year, dynamically when calculating the compoite score column?

Thanks for the expertise.

Cheers.

[cengizhanarslan · Accepted solution]

Step 1) Merge the weights table into your main table

In Power Query, select your main query → Home → Merge Queries → select your weights table → match on the Year column → Join Kind: Left Outer → OK.

Step 2) Expand the merged columns

Click the expand icon on the new merged column → select only the three weight columns (Wage weight, Job Postings weight, Change weight) → uncheck Use original column name as prefix → OK.

Step 3) Replace your custom column formula

Instead of hardcoded values, reference the looked-up weight columns:

= ( [Relative Wage] * [Wage Weight] )
+ ( [Relative Job Postings] * [Job Postings Weight] )
+ ( [Relative Change] * [Change Weight] )

Step 4) Remove the weight columns from the final table if not needed for display

Select the three weight columns → right-click → Remove Columns to keep the output clean.

But keep in mind that:

[ralf_anton]

I can't see your Problem. You have all Columns with the fitting Values in each Row. 

Therefore use them.

= Table.AddColumn(Quelle,"Composite Score", each [Relative Wage] * [Wage] + [Relative Jobs] * [Jobs] + [Relative Change] * [Change])

  Thats meant in the Row for 2023: 0.6 , 0.3 , 0.1

                                        For 2024: 0.5 0.3, 0.2 

e.t.c...

Du benutzt also nicht die Spaltenwerte sondern die Spaltennamen zur Berechnung. 

[Ahmedx]

look at this you need

[ralf_anton]

Hallo,

bin nicht sicher, ob ich Dich richtig verstanden habe. Die Werte stehen doch alle im jeweiligen Datensatz, oder sehe ich das falsch?

Die 0.3 entsprechen doch der Spalte Wage des 1. Datensatzes. Du wirst aber nicht 0.3 * 0.3 rechnen wollen?

Aus meiner Sicht müsstest Du eine Spalte hinzufügen, nennst die "Result" und berechnest die jeweiligen Spaltenwerte mit den Ausgangsspaltenwerten. 

Beispiel: 

Table.AddColumn(Quelle,"Result", each [Relative Wage] * [Wage] + [Relative Jobs] * [Jobs] + ...)

Setzt natürlich voraus, dass es die "Relativ"-Spalten auch gibt. Ansonsten bitte mal genauer erklären...

[metaph0r]

Ich hoffe, meine Absicht wurde nicht falsch verstanden!

Das ist richtig, die Spalten „Relativ“ enthalten bereits vorhandene Werte. Die Idee ist, dass sich das Berichtsjahr basierend auf einem vorhandenen Wert ändert. Beispiel:

[Relativer Lohn] * [Lohn] + [Relative Arbeitsplätze] * [Arbeitsplätze]

Für 2023:

[Relativer Lohn] * [0,3] + [Relative Arbeitsplätze] * [0,6]

Für 2026:

[Relativer Lohn] * [0,2] + [Relative Arbeitsplätze] * [0,3]

Vielen Dank für Ihre Antwort.

------------------------------------------------------------------------------------------

I hope my intentions aren't mistranslated!

That is correct, the "Relative" columns are existing values. Anyways, the idea is for report year to change based on an existing value. Example below:

[Relative Wage] * [Wage] + [Relative Jobs] * [Jobs]

For 2023:
[Relative Wage] * [.3] + [Relative Jobs] * [.6]

For 2026:
[Relative Wage] * [.2] + [Relative Jobs] * [.3]

Thank you for your reply.