Metrics =
var Selection = SELECTEDVALUE('MeasureTable_Metrics'[Category])
RETURN
SWITCH(
TRUE(),
Selection = "Revenue|Actual", MeasureTable_PL[TotalRevenue],
Selection = "Revenue|Budget", MeasureTable_PL[TotalRevenue_Budget],
Selection = "Revenue|Difference ($)", MeasureTable_PL[TotalRevenue] - MeasureTable_PL[TotalRevenue_Budget],
Selection = "Revenue|Difference (%)", (MeasureTable_PL[TotalRevenue] - MeasureTable_PL[TotalRevenue_Budget])/MeasureTable_PL[TotalRevenue_Budget],
Selection = "Revenue|Prior Year", MeasureTable_PL[TotalRevenue_LY],
Selection = "Revenue|Prior Year|Difference ($)", MeasureTable_PL[TotalRevenue] - MeasureTable_PL[TotalRevenue_LY],
Selection = "Revenue|Prior Year|Difference (%)", (MeasureTable_PL[TotalRevenue] - MeasureTable_PL[TotalRevenue_LY])/MeasureTable_PL[TotalRevenue_LY],
Selection = "Cost of Goods Sold|Actual", MeasureTable_PL[TotalCOGS],
Selection = "Cost of Goods Sold|Budget", MeasureTable_PL[TotalCOGS_Budget],
Selection = "Cost of Goods Sold|Difference ($)", MeasureTable_PL[TotalCOGS] - MeasureTable_PL[TotalCOGS_Budget],
Selection = "Cost of Goods Sold|Difference (%)",(MeasureTable_PL[TotalCOGS] - MeasureTable_PL[TotalCOGS_Budget])/MeasureTable_PL[TotalCOGS_Budget],
Selection = "Cost of Goods Sold|Prior Year", MeasureTable_PL[TotalCOGS_LY],
Selection = "Cost of Goods Sold|Prior Year|Difference ($)", MeasureTable_PL[TotalCOGS] - MeasureTable_PL[TotalCOGS_LY],
Selection = "Cost of Goods Sold|Prior Year|Difference (%)", (MeasureTable_PL[TotalCOGS] - MeasureTable_PL[TotalCOGS_LY])/MeasureTable_PL[TotalCOGS_LY],
Selection = "Expenses|Actual", MeasureTable_PL[TotalExpense],
Selection = "Expenses|Budget", MeasureTable_PL[TotalExpense_Budget],
Selection = "Expenses|Difference ($)", MeasureTable_PL[TotalExpense] - MeasureTable_PL[TotalExpense_Budget],
Selection = "Expenses|Difference (%)", (MeasureTable_PL[TotalExpense] - MeasureTable_PL[TotalExpense_Budget])/MeasureTable_PL[TotalExpense_Budget],
Selection = "Expenses|Prior Year", MeasureTable_PL[TotalExpense_LY],
Selection = "Expenses|Prior Year|Difference ($)", MeasureTable_PL[TotalExpense] - MeasureTable_PL[TotalExpense_LY],
Selection = "Expenses|Prior Year|Difference (%)", (MeasureTable_PL[TotalExpense] - MeasureTable_PL[TotalExpense_LY])/MeasureTable_PL[TotalExpense_LY],
Selection = "Blank", 0,
0
All works fine however, I do not want to show the colmn 'Category' in my visual as this is used solely to determine the values for the field 'Metrics
When I removed the field 'Category' the measure 'Metrics' no longer calculates correctly - this is expected behaviour as the measure used SELECTEDVALUE which is contexual (all values become 0 for the measure).
Is there a way to reference the Catgeory column without disaplying it on the visual and still have the measure calculate correctly?
