Forum Discussion
include extra column - target
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 | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
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.
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 ?