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

Yes I have modified the mesure and column to get the account number from the dimension table as below but still the values is zero. 

 

52500 = IF(HASONEVALUE('LedgerTable Dim'[Accountnum]),IF(VALUES('LedgerTable Dim'[Accountnum])="14850",CALCULATE(SUM('LedgerTrans Fact'[AmountMST]),'LedgerTable Dim'[Accountnum]="52500"),0),BLANK())

 


52500Col = IF(RELATED('LedgerTable Dim'[Accountnum])="14850",CALCULATE (
SUM ( 'LedgerTrans Fact'[AmountMST] ),
FILTER (ALL( 'LedgerTrans Fact'), RELATED('LedgerTable Dim'[Accountnum]) = "52500" )
),0)

 

 

 

 

Thanks

Smita

sjoshi's avatar
sjoshi
Helper II
7 years ago

Hello

 

To explain more

In Account slicer we are selecting account number 14800. This Account slicer value is coming from Ledgertable dimension. LedgerTransFact.Accountnum (fact table) =Ledgertable.accountnum (Dimension table).  

When the slicer value is 14800 then get the sum of amount from ledgertransfact table for account num 52000. I have tried below way also still it is giving zero value not sure what is incorrect 

 

52500 = IF(HASONEVALUE('LedgerTable Dim'[Accountnum]),IF(VALUES('LedgerTable Dim'[Accountnum])="14850",CALCULATE(SUM('LedgerTrans Fact'[AmountMST]),'LedgerTrans Fact'[ACCOUNTNUM]="52500"),0),BLANK())

 

 

52500Col = IF(RELATED('LedgerTable Dim'[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

    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)