include extra column - target

[jps_HHH]

I've created a matrix table with the following charateritics:

 - Rows:  are multiple measures that I created. 

 - Columns:  are months over the year

In order to put the measures as rows and to display their names, I created a "measures table" to list all names of the measures and created a measure that contains inside all measures:
    

Selected Measure Value =

SWITCH(

    SELECTEDVALUE('Measures Table'[Key Performance Indicator]),

    "year Sales",[ year Sales],

    "Training", [Training],

    "Quality ", [Quality - Issues/Batch],

)

The table is perfect. Howerver, I want to include an extra column with the target for each measure (this target is for end of the year i.e. I want a single column and not to repeat in each month). 
Is it possible ? 
 

[Anonymous]

Hi @jps_HHH,

As we haven’t heard back from you, we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @jps_HHH,

As we haven’t heard back from you, we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @jps_HHH,

As we haven’t heard back from you, we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[pankajnamekar25 · Accepted solution]

Hello @jps_HHH 

try this solution

Custom Column =

UNION(

    SELECTCOLUMNS('DateTable', "Month", FORMAT('DateTable'[Date], "MMM YYYY")),

    DATATABLE("Month", STRING, {{"Target"}})

)

 Update your value measure like this

Final Value =

VAR SelectedKPI = SELECTEDVALUE('Measures Table'[Key Performance Indicator])

VAR CurrentCol = SELECTEDVALUE('Month_Column_Table'[Month])

RETURN

SWITCH(

    TRUE(),

    CurrentCol = "Target",

        SWITCH(

            SelectedKPI,

            "year Sales", [year Sales Target],

            "Training", [Training Target],

            "Quality", [Quality Target]

        ),

    -- Else return monthly values

    SWITCH(

        SelectedKPI,

        "year Sales", CALCULATE([year Sales], 'DateTable'[MonthYear] = CurrentCol),

        "Training", CALCULATE([Training], 'DateTable'[MonthYear] = CurrentCol),

        "Quality", CALCULATE([Quality - Issues/Batch], 'DateTable'[MonthYear] = CurrentCol)

    )

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[bhanu_gautam]

@jps_HHH You need to create a measure that will return the target value based on the selected measure. You can use a similar SWITCH function to achieve this

DAX
Target Value =
SWITCH(
SELECTEDVALUE('Measures Table'[Key Performance Indicator]),
"year Sales", [year Sales Target],
"Training", [Training Target],
"Quality", [Quality Target]
)

In your matrix table, you should have the rows as the measures from the 'Measures Table' and the columns as the months. You can then add the Selected Measure Value measure to the values section of the matrix table. Additionally, add the Target Value measure to the values section to display the target values in a separate column.

[jps_HHH]

thanks for the reply. 
I want a single column for target. If a add Target value measure to the values section, the table will repeat target column in each month, right ?