Forum Discussion

sjoshi's avatar
sjoshi
Icon for Helper II rankHelper II
7 years ago

IF, FILTER SUM Function

Hello,   I'm trying to do one simple thing but not sure why filter is not working   If accountnum ="14850" the take sum of amount for accountnum "52500" else null. This statement I have tried to ...
Need Help
Agreenwood's avatar
Agreenwood
Icon for Helper I rankHelper I
7 years ago

And this formula doesn't work?

 

52500 = IF('LedgerTrans Fact'[AccountNum]="14850",
CALCULATE (
SUM ( 'LedgerTrans Fact'[AmountMST] ),
FILTER (ALL( 'LedgerTrans Fact'), 'LedgerTrans Fact'[AccountNum] = "52500" )
),0)

 

This formula should sum all the AmountMST for account number 52500, though things such as filters and relationships could be stopping it from working. Do you have a sample/retracted workbook you could share?

sjoshi's avatar
sjoshi
Icon for Helper II rankHelper II
7 years ago

Hello

I have written below measure by removing the if statement then its giving me total sum 

select sum(amountmst) from ledgertrans_fact where ledgertrans.accountnum='52500' its not applying other time and project slicers and retruning same value in each row.

  

52500 = CALCULATE (
SUM ( 'LedgerTrans Fact'[AmountMST] ),
FILTER ( ALL('LedgerTrans Fact'), 'LedgerTrans Fact'[AccountNum] = "52500"))

 

If I take below mesure then its giving probelm in IF condition. 

52500 = IF('LedgerTrans Fact'[AccountNum]="14850",
CALCULATE (
SUM ( 'LedgerTrans Fact'[AmountMST] ),
FILTER (ALL( 'LedgerTrans Fact'), 'LedgerTrans Fact'[AccountNum] = "52500" )
),0)

 

  • sjoshi's avatar
    sjoshi
    Icon for Helper II rankHelper II
    7 years ago

    Also when I add 52500 measure in values then all other working columns such amountmst, prior quarter etc are not working fine.

     

     

  • Agreenwood's avatar
    Agreenwood
    Icon for Helper I rankHelper I
    7 years ago

    Please try: 

    52500 = IF('LedgerTrans Fact'[AccountNum]="14850",
CALCULATE (
SUM ( 'LedgerTrans Fact'[AmountMST] ),
FILTER (ALLEXCEPT( 'LedgerTrans Fact','LedgerTrans Fact'[Project Number], 'LedgerTrans Fact'[Quarters to Current] ), 'LedgerTrans Fact'[AccountNum] = "52500" )
),0)

     

    You'll have to name the "Project Number" and "Quarters to Current" columns to their actual names. Also please excuse any syntax issues... just typed it out on mobile.

    • sjoshi's avatar
      sjoshi
      Icon for Helper II rankHelper II
      7 years ago

      Thanks for your reply but in measure if is not showing the fact columns. Also project number and Quarters to current are coming from project dimension and time dimension respectively and Related function is not working in ALLEXCEPT

       

      52500 = IF('LedgerTrans Fact'[AccountNum]="14850",
CALCULATE (
SUM ( 'LedgerTrans Fact'[AmountMST] ),
FILTER (ALLEXCEPT( 'LedgerTrans Fact','LedgerTrans Fact'[Project Number], 'LedgerTrans Fact'[Quarters to Current] ), 'LedgerTrans Fact'[AccountNum] = "52500" )
),0)

       Thanks

      Smita

      • sjoshi's avatar
        sjoshi
        Icon for Helper II rankHelper II
        7 years ago

        Okay updated results

         

        52500newnew = IF('LedgerTrans Fact'[AccountNum]="14850",
        CALCULATE (
        SUM ( 'LedgerTrans Fact'[AmountMST] ),
        FILTER (ALLEXCEPT( 'LedgerTrans Fact','Department Dim'[Department Number], 'Time Dim'[Fiscal QuarterName] ),
        'LedgerTrans Fact'[AccountNum] = "52500" )
        ),0)

         

        This above measure able to write and save but results are not matching with the database.  Values are much higher than actual. Only three slicers are present in report which we are covering above.

        One report level filter with company number="999" is remaining where should I write that?

        I have tried in ALLEXPECT in below ways but results are still not matching

         Attempt 1: This is a column not measure

        52500newnew = IF('LedgerTrans Fact'[AccountNum]="14850" ,
        CALCULATE (
        SUM ( 'LedgerTrans Fact'[AmountMST] ),
        FILTER (ALLEXCEPT( 'LedgerTrans Fact','Department Dim'[Department Number], 'Time Dim'[Fiscal QuarterName] ,'Company Dim'[Company Number]),
        'LedgerTrans Fact'[AccountNum] = "52500" )
        ),0)

         

        Attempt 2:This is a column not measure

        52500newnew = IF('LedgerTrans Fact'[AccountNum]="14850" ,
        CALCULATE (
        SUM ( 'LedgerTrans Fact'[AmountMST] ),
        FILTER (ALLEXCEPT( 'LedgerTrans Fact','Department Dim'[Department Number], 'Time Dim'[Fiscal QuarterName] ),
        ('LedgerTrans Fact'[AccountNum] = "52500") && (RELATED('Company Dim'[Company Number])="999") )
        ),0)

         

        Thanks

        Smita