Forum Discussion
Helper IIIF, 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 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
19 Replies
- Ashish_Mathur
Super User
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.
sjoshiHi 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
Community Champion
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)
AgreenwoodHelper I
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
- Ashish_Mathur
Hi,
I am out of ideas now.
