Vary the calculated column formula

[markperrone]

Not sure which visual will be the most appropriate for this use case.

We have a table of various metrics (# of top 20 suppliers, Gross revenue, Shipments, etc) and we're looking to show these metrics for any two months and current YTD.  The month is selectable so the example below could be for Jan-Feb.  Think the 3 columns will be a calculated column with a SWITCH to control which metric is calculated but it didn't work.  Wondering if matrix is the better solution.  Any thoughts are appreciated.  Thanks.

Metric	Mar 2025	Apr 2025	Jan-Apr 2025
Top 20 suppliers	342	344	347
Gross revenue	$10,000,000	$11,000,000	$13,000,000
Shipments	42,000	44,000	51,000

[v-sdhruv]

Hi @markperrone  ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[v-sdhruv]

Hi @markperrone ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[v-sdhruv]

Hi @markperrone ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[DataNinja777 · Accepted solution]

Hi @markperrone ,

You should use a matrix visual instead of calculated columns because calculated columns are static and do not respond to slicer selections. To show metrics like Top 20 suppliers, Gross revenue, and Shipments across two selected months and YTD, start by reshaping your data into a long format with columns for Metric, Date, and Value. Then, create a single DAX measure:

Metric Value = SUM('MetricsTable'[Value])

In the Date table, create a calculated column that assigns labels like "Month1", "Month2", and "YTD" based on user selections:

Selected Label = 
SWITCH(TRUE(),
    'Date'[Month] = SELECTEDVALUE('MonthSelector'[Month1]), "Month1",
    'Date'[Month] = SELECTEDVALUE('MonthSelector'[Month2]), "Month2",
    'Date'[Date] IN DATESYTD('Date'[Date]), "YTD"
)

Use this new label as the column field in the matrix. Put Metric on the rows, the Selected Label on the columns, and Metric Value as the values. This setup keeps your model dynamic and responsive to slicers. Do not use SWITCH inside a calculated column to try and change metrics—it won’t work because calculated columns are evaluated during data load, not when slicers change. Measures and matrix visuals are designed to handle exactly this kind of flexible layout.

Best regards,