Forum Discussion

Sujitkumarjena's avatar
Sujitkumarjena
Copper Contributor
Nov 27, 2021
Solved

Custom calculated field that dynamically change with filter

Hi, I am trying to create a calculated field that changes with pivot filter. However, it doesn't seem to work. Is there another way to build this because i tried power query and power pivot as well ...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 27, 2021

    Sujitkumarjena 

    You have bit different data in the table and in data model. If take data model as the basis you may add measure

    some:=VAR revenue =
    SUM ( Table1[Revenue in CC] )
VAR FTEs =
    SUM ( Table1[FTE] )
VAR r21 =
    DIVIDE ( revenue * 1000, FTEs / 12, 0 )
VAR r22 =
    DIVIDE ( revenue * 1000, FTEs * 6 / 12, 0 )
VAR rOther = FTEs * 12 / 14
RETURN
    IF (
        ISFILTERED ( Table1[Year] ),
        IF ( VALUES ( Table1[Year] ) = "FY21", r21, r22 ),
        rOther
    )

    PivotTable build from data model is in Sheet1.