Forum Discussion
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 ...
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.
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.
SergeiBaklan : Thanks a Lot. This works!
Sujitkumarjena , you are welcome
