Forum Discussion
IF, FILTER SUM Function
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
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
Please try these formulas as measures, not columns.
Hello
My teammate updated sample pbix is below ticket.
Thanks
Smita
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
