Add target column to calculated table

Question

Hi,&nbsp;I have calculated column and I want to add target for that and get difference value. How can I do this.&nbsp;Name,Open,Close and total is calculated table and Target, Difeerence column I want to add&nbsp;NameOpenCloseTotalTargetDifferenceA1020305020B56116049C20507010030&nbsp;How can I add this and display.&nbsp;Regards,Aditya Vighne

anonymous · Answer

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 · Answer

Anonymous&nbsp;&nbsp;I want to add target for each by hard coding.

anonymous · Answer

Hi,&nbsp;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)&nbsp;RegardsRohit

ashish_mathur · Answer

Hi,
Create a seperate 2 column Table with Names in column A and targets in column B.&nbsp; Create a relationship from the Names in your existing table to the Names in the new 2 column table.&nbsp; In your existing table, write these calculated column formulas:
Targets = related(table1[Target])
Difference = [targets]-[total]
Hope this helps.

allisonkennedy · Answer

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

Forum Discussion

Add target column to calculated table

10 Replies

Related content

TMDL - calculated column

Calculating Days late with specific target times

Direct Lake Model - Calculated Column/Tables

Daily Sales Target Calculation

include extra column - target

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