Forum Discussion
How to dynamically calculate row total?
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
- create an explicit measure instead of an implicit measure (calculated column)
read://https_support.microsoft.com/?url=https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fmeasures-in-power-pivot-86484821-a324-4da3-803b-82fd2e5033f4 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.
