Nov 27 2021 12:42 PM
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.
Nov 27 2021 02:06 PM
SolutionYou 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.
Nov 27 2021 02:34 PM
maybe you will find this helpful
Nov 29 2021 10:45 AM
@Sergei Baklan : Thanks a Lot. This works!
Nov 29 2021 11:30 AM
@Sujitkumarjena , you are welcome
Nov 27 2021 02:06 PM
SolutionYou 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.