Sumif for journal entry table

[Murphs02]

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!

[Murphs02 · Accepted solution]

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]))

[Murphs02]

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

[Ashish_Mathur]

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.