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