How to dynamically calculate row total?

Copper Contributor



I organized my data in power query and used power pivot to create a pivot table off that data model. I have the years 2019-2023 as columns in this current dataset. However, sometimes when I refresh, a 2024 column will be added. How do I create a new measure column that will take the sum of columns 2019:2023 as well as 2019:2024 without me having to manually edit the formula to include the new column?


Additionally, I have metrics that I will be conditionally formatting this table on. For example, I will want all values less than 100,000 filled green. How do I make this dynamic so that it works with the occasional inclusion of another column (2024)? I've tried but when I select 2019:2023 as my value range, it hard codes the formula and does not apply to the new column.

3 Replies


It seems you did pivot the data inside PQ. And then you did another pivot on top of that which is total ...


The query labeled "3 - Pivot" was just me pivoting the rows into columns so that it displays the year as columns instead of rows.
create an explicit measure instead of an implicit measure (calculated column)