Forum Discussion
include extra column - target
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:
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 ?
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.
6 Replies
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 ?
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.
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 PabbuHi @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 PabbuHi @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
