Forum Discussion

sjoshi's avatar
sjoshi
Helper 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
sjoshi's avatar
sjoshi
Helper II
7 years ago

Hello

 

After further analysis I saw that yes fact table account number and the dimension (Slicer Account) accountnum values are same for given record thats why sum is coming zero all the time in power BI but still I need to achieve that If statement. Is there any way to do that?

 

If LedgertableDimension.accountnum = "14850" then take sum amountmst for LedgertransFact.accountnum="52500" else null

 

Slicer: LedgertableDimension.accountnum

 

Thanks

Smita

Agreenwood's avatar
Agreenwood
Helper 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
    Helper 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
      Helper 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
      Helper 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
        Helper 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