IF, FILTER SUM Function

[sjoshi]

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 write in below way and its giving incorrect sum value. What am I doing incorrect 

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

Thanks

Smita

[Agreenwood]

It's always going to return 0 because you are stating where account num = 14850 then sum where account number = 52500. You can't be both 14850 and 52500 at the same time.

[sjoshi]

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]

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]

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]

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]

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)

 

[Agreenwood]

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]

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]

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

[Agreenwood]

Please try these formulas as measures, not columns.

[sjoshi]

Hello

My teammate updated sample pbix is below ticket. 

https://community.powerbi.com/t5/Desktop/Excluding-filter-for-the-measured-column-in-Matrix/m-p/5265...

Thanks

Smita 

[sjoshi]

Hello

If I write below measure its brings all the project numbers which not even related to this account and the list is more and we are looking for only project numbers which are associated 

52505 =
IF (
MIN('LedgerTable Dim'[Accountnum]) = "14825",
CALCULATE (
SUM ( 'LedgerTrans Fact'[AMOUNTMST] ),
FILTER (
ALLEXCEPT (
'LedgerTrans Fact',
'Project Dim',
'Time Dim'
),
'LedgerTrans Fact'[ACCOUNTNUM] = "52505"
&& RELATED('Company Dim'[Company Number])= "999"
)
),
0
)

 

Only on value can be selected in Account slicer out of 9 values. Account slicer is restricted only for those 9 values. For those 9 values only one of the 9 output will be true.

EX If Account slicer is 14850 then get the sum of amountmst from fact for account 52500. First three columns(Current QTR, Prior QTR, Balance as of End of QTR)  in the output are for account which is selected in slicer.

LedgerTable.ACCOUNTNUM.equals("14850")?"52500":

LedgerTable.ACCOUNTNUM.equals("14830")?"53100":

LedgerTable.ACCOUNTNUM.equals("14825")?"52505":

LedgerTable.ACCOUNTNUM.equals("14821")?"52201":

LedgerTable.ACCOUNTNUM.equals("14820")?"52100":

LedgerTable.ACCOUNTNUM.equals("14805")?"51500":

LedgerTable.ACCOUNTNUM.equals("14802")?"51201":

LedgerTable.ACCOUNTNUM.equals("14801")?"51200":

LedgerTable.ACCOUNTNUM.equals("14800")?"51100"

-- 52500
select sum(amountmst) from WH_LEDGERTRANS_F
where ACCOUNTNUM like '52500' and (DIMENSION4_PROJECT='30648' )
and DATAAREAID='999'
and TRANSDATE >='1/1/2017' and TRANSDATE <='3/31/2017'; -- 43434.48

--Current QTR for 14850
select sum(amountmst) from WH_LEDGERTRANS_F
where ACCOUNTNUM like '14850' and (DIMENSION4_PROJECT='30824' )
and DATAAREAID='999'
and TRANSDATE >='1/1/2017' and TRANSDATE <='3/31/2017'; -- negative 43434.48

--Prior QTR END for 14850
select sum(amountmst) from WH_LEDGERTRANS_F
where ACCOUNTNUM like '14850' and (DIMENSION4_PROJECT='30648') --30648
and DATAAREAID='999'
and TRANSDATE >='7/1/2017' and TRANSDATE <='12/31/2017';

Thanks

Smita

[sjoshi]

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

[Ashish_Mathur]

Hi,

I am out of ideas now.

[Ashish_Mathur]

Hi,

Try this measure

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

Hope this helps.

[sjoshi]

 Hi Ashish

I have tried to create measure in above way but ots returing always 0 value. Also I have created column with Greg's solution which is by adding all but both did not work and in values are 0 for 52500 column

Thanks

Smita

[Greg_Deckler]

Could be a number of things. If you [AccountNum] is numeric, then comparing it to text won't work, you would need to remove the quotes. Are you getting an error?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[sjoshi]

Hi Greg 

I'm not getting any error.  Accountnum is text column

Thanks

Smita

[Greg_Deckler]

OK, so is the formula presented a measure or a column?

Sample data would be really helpful as well. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Although, I'm thinking:

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