Forum Discussion
Vary the calculated column formula
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 |
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,
4 Replies
- DataNinja777
Super User
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,
v-sdhruvCommunity Support
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 - 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