Add target column to calculated table

[adityavighne]

Hi,

I have calculated column and I want to add target for that and get difference value. How can I do this.

Name,Open,Close and total is calculated table and Target, Difeerence column I want to add

Name	Open	Close	Total	Target	Difference
A	10	20	30	50	20
B	5	6	11	60	49
C	20	50	70	100	30

How can I add this and display.

Regards,

Aditya Vighne

[Icey]

Hi @adityavighne ,

Please provide more details to help us understand what you want.

Best Regards,

Icey

[Icey]

Hi @adityavighne ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,
Icey

[adityavighne]

No this problem is still not solved

[AllisonKennedy]

Please can you provide further detail then on what hasn't worked about the suggested solutions?

[Ashish_Mathur]

Hi,

Create a seperate 2 column Table with Names in column A and targets in column B.  Create a relationship from the Names in your existing table to the Names in the new 2 column table.  In your existing table, write these calculated column formulas:

Targets = related(table1[Target])

Difference = [targets]-[total]

Hope this helps.

[Anonymous]

You have targets for each "Name" in a different table which you want to bring in this calculated table or you want to add the values by hard-coding them in the DAX and add it here?

[adityavighne]

@Anonymous  

I want to add target for each by hard coding.

[Anonymous]

I think you should maintain your targets in a separate table, but if you want to hard-code it, you could do so as others suggested here using SWITCH. Refer to the screenshot below..

[AllisonKennedy]

@adityavighne I think creating a table as @Anonymous suggested and loading that into the Data Model would be a better solution, but you could use a Switch or IF to hard code this using ADDCOLUMNS or similar table constructor:

NewTable= ADDCOLUMNS(CalculatedTable, "Target", SWITCH(CalculatedTable[Name], "A", 50, "B", 60, "C", 100)

The difference can be added as a Calculated column or added into that table expression.

See this solution from @v-lili6-msft for what we mean by having the target table stored in the data model and how to set that up with relationships (in your case replace 'year' with 'name')
https://community.powerbi.com/t5/Desktop/Adding-annual-target-to-existing-table/td-p/579550

[Anonymous]

Hi,

You can use SWITCH statement to list target as 50 for A , 100 for B , 150 for C etc. SWITCH ("ColumnA","A",50,"B",100,"C",150,0)

Regards

Rohit