Forum Discussion
Sumif for journal entry table
Hi
I am new to Power BI so bear with me!
I am building. a report with sap data where I have multiple lines for journal numbers. I need to keep each line as I report on some information on line item level.
So I have a column in my data table with journalid #s
Each journal has several lines, a minimum of two but can be any number. Each line will have an amount totalling to zero for the entire journal (debits and credits).
I need to get the #of journals under a certain value. It's no problem to calculate the value of the total journal (abs value of all lines/2), but I am struggling to get the #s journals under x value.
I can do with a sumif and filter on excel but on power BI it's picking up small values on indiv lines rather than total of abs value /2.
I have tried with sum and filter in DAX without success.
hoping someone on here can help as I am sure am missing something simple
thanks!
Hi , I got there eventually :-)with sum and Allexcept for the sumif filters.
JOURNAL ENTRY TOTAL VALUE = CALCULATE(SUM('ACDOCA EXTRACT JE'[Amount in Global Currency - ABS /2]),ALLEXCEPT('ACDOCA EXTRACT JE','ACDOCA EXTRACT JE'[Journal Entry],'ACDOCA EXTRACT JE'[Fiscal Year],'ACDOCA EXTRACT JE'[Company Code]))
3 Replies
Hi , I got there eventually :-)with sum and Allexcept for the sumif filters.
JOURNAL ENTRY TOTAL VALUE = CALCULATE(SUM('ACDOCA EXTRACT JE'[Amount in Global Currency - ABS /2]),ALLEXCEPT('ACDOCA EXTRACT JE','ACDOCA EXTRACT JE'[Journal Entry],'ACDOCA EXTRACT JE'[Fiscal Year],'ACDOCA EXTRACT JE'[Company Code]))Hi,
Share the download link of the MS Excel file with your formulas/filters/Pivot/Comments Tables so that the logic can beunderstood and translated in the DAX formula language.
VALUE=IFS(SUMIF(E:E,E3,J:J)<1000,"UNDER 1K",SUMIF(E:E,E3,J:J)<5000,"UNDER 5K",SUMIF(E:E,E3,J:J)>5000,"NA")
Pivot with distinct count
Row LabelsDistinct Count of Document NumberGrand Total234
NA 88 UNDER 1K 70 UNDER 5K 76 sample data
Posting Date Entry Date Time of Entry Company Code Document Number Document type G/L Account Company Code Currency Key Company Code Currency Value ABS/2 VALUE Document Date 31/05/2023 07/06/2023 11:35:37 US11 100221993 XX 123510 USD 0.00 0.00 UNDER 1K 31/05/2023 31/05/2023 07/06/2023 14:27:16 US11 100222133 XX 410909 USD 0.00 0.00 UNDER 1K 31/05/2023 31/05/2023 07/06/2023 20:33:06 US11 100222394 XX 123010 USD -2,948,682.83 1,474,341.42 NA 31/05/2023 31/05/2023 07/06/2023 15:56:11 US11 100222229 XX 131500 USD -74,524.10 37,262.05 NA 31/05/2023 31/05/2023 07/06/2023 15:56:11 US11 100222229 XX 131500 USD -2,063.42 1,031.71 NA 31/05/2023 31/05/2023 07/06/2023 21:54:53 US11 100222425 XX 560200 USD -905,056.00 452,528.00 NA 31/05/2023 31/05/2023 07/06/2023 15:56:11 US11 100222229 XX 560000 USD 85,230.82 42,615.41 NA 31/05/2023 31/05/2023 07/06/2023 15:56:11 US11 100222229 XX 560000 USD 12,504.51 6,252.26 NA 31/05/2023 31/05/2023 07/06/2023 15:56:11 US11 100222229 XX 131500 USD -8,969.84 4,484.92 NA 31/05/2023 31/05/2023 07/06/2023 15:56:11 US11 100222229 XX 131500 USD -38,842.16 19,421.08 NA 31/05/2023 31/05/2023 07/06/2023 11:35:37 US11 100221993 XX 231510 USD 0.00 0.00 UNDER 1K 31/05/2023 31/05/2023 07/06/2023 15:56:11 US11 100222229 XX 560000 USD 588.52 294.26 NA 31/05/2023 31/05/2023 07/06/2023 21:54:53 US11 100222425 XX 134600 USD 905,056.00 452,528.00 NA 31/05/2023 31/05/2023 07/06/2023 20:33:06 US11 100222394 XX 123010 USD 2,944,306.85 1,472,153.43 NA 31/05/2023 31/05/2023 07/06/2023 15:56:11 US11 100222229 XX 560000 USD 37,076.61 18,538.31 NA 31/05/2023 31/05/2023 07/06/2023 20:33:06 US11 100222394 XX 560200 USD 12,531,076.29 6,265,538.15 NA 31/05/2023 31/05/2023 07/06/2023 20:33:06 US11 100222394 XX 560200 USD -2,944,306.85 1,472,153.43 NA 31/05/2023 31/05/2023 07/06/2023 15:56:11 US11 100222229 XX 131500 USD -1,129.29 564.65 NA 31/05/2023 31/05/2023 07/06/2023 15:56:11 US11 100222229 XX 560000 USD 8,969.84 4,484.92 NA 31/05/2023 31/05/2023 07/06/2023 15:56:11 US11 100222229 XX 131500 USD -37,076.61 18,538.31 NA 31/05/2023
