SOLVED

Custom calculated field that dynamically change with filter

%3CLINGO-SUB%20id%3D%22lingo-sub-3010863%22%20slang%3D%22en-US%22%3ECustom%20calculated%20field%20that%20dynamically%20change%20with%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3010863%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20calculated%20field%20that%20changes%20with%20pivot%20filter.%20However%2C%20it%20doesn't%20seem%20to%20work.%20Is%20there%20another%20way%20to%20build%20this%20because%20i%20tried%20power%20query%20and%20power%20pivot%20as%20well%20and%20they%20don't%20work.%20i%20have%20attached%20a%20sample%20file%20with%20pivot%20for%20reference.%20Any%20suggestions%20will%20be%20helpful.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3010863%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3010942%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20calculated%20field%20that%20dynamically%20change%20with%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3010942%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1230977%22%20target%3D%22_blank%22%3E%40Sujitkumarjena%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20have%20bit%20different%20data%20in%20the%20table%20and%20in%20data%20model.%20If%20take%20data%20model%20as%20the%20basis%20you%20may%20add%20measure%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3Esome%3A%3DVAR%20revenue%20%3D%0A%20%20%20%20SUM%20(%20Table1%5BRevenue%20in%20CC%5D%20)%0AVAR%20FTEs%20%3D%0A%20%20%20%20SUM%20(%20Table1%5BFTE%5D%20)%0AVAR%20r21%20%3D%0A%20%20%20%20DIVIDE%20(%20revenue%20*%201000%2C%20FTEs%20%2F%2012%2C%200%20)%0AVAR%20r22%20%3D%0A%20%20%20%20DIVIDE%20(%20revenue%20*%201000%2C%20FTEs%20*%206%20%2F%2012%2C%200%20)%0AVAR%20rOther%20%3D%20FTEs%20*%2012%20%2F%2014%0ARETURN%0A%20%20%20%20IF%20(%0A%20%20%20%20%20%20%20%20ISFILTERED%20(%20Table1%5BYear%5D%20)%2C%0A%20%20%20%20%20%20%20%20IF%20(%20VALUES%20(%20Table1%5BYear%5D%20)%20%3D%20%22FY21%22%2C%20r21%2C%20r22%20)%2C%0A%20%20%20%20%20%20%20%20rOther%0A%20%20%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EPivotTable%20build%20from%20data%20model%20is%20in%20Sheet1.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3010970%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20calculated%20field%20that%20dynamically%20change%20with%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3010970%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1230977%22%20target%3D%22_blank%22%3E%40Sujitkumarjena%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emaybe%20you%20will%20find%20this%20helpful%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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!