Create a Column using values from Parameter

[abdulmannan1991]

Hello, Please assist with the following challenge:

Task: I want to create a column in a custom table (Month 1 to 24) which should take values from a What-IF Parameter. Any change in Parameter slicer value should be reflected in that column.

Challenge: I was able to create a column but it always takes the 'default' value of paramter (which I defined as 2,000). If I change the value on Paramter slicer, the column doesn't pick the new value. And the the sum of column values also remains same (that is, the sum of 24 times 2,000 = 48,000).

Process: Here's the step-by-step process which I followed:

STEP 1: I created a table "MyTable" with 2 columns, Sr and Month (24 rows).

STEP 2: I created a What-If Parameter "MyFees" with default value of 2,000.

STEP 3: I added a new column to MyTable using following statement:

                       MyTotalFees = MyFees [MyFees Value]

I wanted this new column "MyTotalFees" to take values from Parameter (created in Step 2).

But it only takes the 'default' value of Paramter, which was 2,000.

STEP 4: I created the visuals for verification and realized that the new column (MyTotalFees) only takes the default value (2,000) even if I change the Parameter slicer to any other value (like, 1,000)... And hence the sum of column values also remains static (that is, 24 x 2,000 = 48,000).

Please help me creating this table which should take values from a paramter (slicer) dynamically.

I have attached the screenshots of STEP 1 to 4 as well for reference.

Thank you.

Screenshots:

STEP 1STEP 2STEP 3STEP 4

[dax · Accepted solution]

Hi @abdulmannan1991 , 

Yes, as @nandukrishnavs  mentioned, the slicer can't change calculated column dynamically, so you need to use measure to achieve this goal, you could try below measure

total = IF(HASONEVALUE('Month table'[MONTH NAME]), SUM('MONTH'[MONTH]), SUM('MONTH'[MONTH])*24)

Best Regards,
Zoe Zhi

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

[abdulmannan1991]

Hi @dax 

Thank you, the DAX worked!!

Can you help me a bit further in achieving the target - as described below:

I created a table (MyTable) with 2 columns, as follows:

Month Number	Month
1	Month 1
2	Month 2
3	Month 3
4	Month 4

I created a What-IF Parameter (slicer) to select the Annual Spend value.

Then, I created the following Measure to calculate the Monthly Spend and to show the total sum:

Monthly Spend = IF(HASONEVALUE(MyTable[Month]), SUM('Annual Spend'[Annual Spend])/12, (SUM('Annual Spend'[Annual Spend])/12)*MAX(MyTable[Month Number]))

I actually need to apply some Savings % to each month separately, and then calculate the Savings per month and the Sum of total savings, so I created 4 What-IF Parameters (slicers) for the selection of monthly savings %. And then I created the following Measure to calculate the monthly savings and sum of total savings:

Total Savings = 
IF(SUM(MyTable[Month Number])<=1, [Monthly Spend] * 'Savings % (Month 1)'[Savings % (Month 1) Value],
    IF(SUM(MyTable[Month Number])<=2, [Monthly Spend] * 'Savings % (Month 2)'[Savings % (Month 2) Value],
        IF(SUM(MyTable[Month Number])<=3, [Monthly Spend] * 'Savings % (Month 3)'[Savings % (Month 3) Value],
            IF(SUM(MyTable[Month Number])<=4, [Monthly Spend] * 'Savings % (Month 4)'[Savings % (Month 4) Value],
               
                ([Monthly Spend] * ('Savings % (Month 1)'[Savings % (Month 1) Value] + 'Savings % (Month 2)'[Savings % (Month 2) Value] + 'Savings % (Month 3)'[Savings % (Month 3) Value] + 'Savings % (Month 4)'[Savings % (Month 4) Value]))

))))

The above Measure gives me correct values for Monthly Savings but doesn't give the correct total sum!! I know the DAX is incorrect in the ELSE statement, but I don't know the correct answer. May be the ELSE statement is wrong, or my whole DAX or Process is wrong for the calculation of monthly sum of savings and the total sum!!

Please can you assist with the correct DAX? Or may be suggest an alternative for the whole DAX/Measure/Process, etc.

For reference, the base file is available on below-given dropbox link.

https://www.dropbox.com/t/US9uDK9V6srQCJwo

Attached a snapshot from the Pbix.

For example, For the Month Number up to 3 and Annual Spend of 1,200,000, and the savings % for month 1, 2 and 3 being 20%, 10% and 5% respectively, the savings per month (in table) are correct, that is, 20,000, 10,000 and 5,000... However the total sum comes out to be 108,000 which is incorrect (and it should ideally be 20,000 + 10,000 + 5,000 = 35,000).  

Thank you.

[nandukrishnavs]

@abdulmannan1991  - We cannot create a dynamic calculated column based on what-if parameter. Instead, I would recommend you to create a measure. 

Please refer to this link-https://community.powerbi.com/t5/Desktop/Calculated-Column-Table-Change-Dynamically-According-to-Sli...

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂