Forum Discussion
Solved
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 ite...
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]))
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 |