Forum Discussion

JackFrench11's avatar
JackFrench11
Copper Contributor
May 02, 2024

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

  • Yea_So's avatar
    Yea_So
    Bronze Contributor
    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
    • JackFrench11's avatar
      JackFrench11
      Copper Contributor
      The query labeled "3 - Pivot" was just me pivoting the rows into columns so that it displays the year as columns instead of rows.

Resources