SOLVED

Custom calculated field that dynamically change with filter

Copper Contributor

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 and they don't work. i have attached a sample file with pivot for reference. Any suggestions will be helpful. 

4 Replies
best response confirmed by Sujitkumarjena (Copper Contributor)
Solution

@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.

@Sujitkumarjena 

maybe you will find this helpful

@Sergei Baklan : Thanks a Lot. This works!

1 best response

Accepted Solutions
best response confirmed by Sujitkumarjena (Copper Contributor)
Solution

@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.

View solution in original post