Group By with filtered calculations

mjgenesis — Mon, 01 May 2023 15:22:09 GMT

Hello all, looking for some help with the creation of a table of grouped data from another table. First I'll provide a sample of the data from the old table (below) and then the formula I'm using to create the new one with the actual and expected results.

SortDateCode	Product	SalesRep	Customer Code	Customer	TransactionID	Type	TXAmount
202207	A	10040	999999	Company1	37781	Purchase	14585.2
202207	B	10040	999999	Company1	36169	Purchase	12329.55
202207	C	10040	999999	Company1	35409	Purchase	9425.58
202207	D	10040	999999	Company1	38348	Purchase	8236.62
202207	E	10040	999999	Company1	35716	Purchase	6818.93
202207	F	10040	999999	Company1	37018	Purchase	6034.93
202207	A	10040	999999	Company1	38628	Purchase	5137.25
202207	B	10040	999999	Company1	38193	Purchase	1587.68
202207	C	10040	999999	Company1	38473	Purchase	1566.02
202207	D	10040	999999	Company1	36182	Purchase	1455.24
202207	E	10040	999999	Company1	36509	Purchase	1427.63
202207	F	10040	999999	Company1	35715	Purchase	1414.96
202207	A	10040	999999	Company1	37159	Purchase	1403.72
202207	B	10040	999999	Company1	37156	Purchase	981.02
202207	C	10040	999999	Company1	37515	Purchase	963.5
202207	D	10040	999999	Company1	38624	Purchase	870.86
202207	E	10040	999999	Company1	38347	Credit	777.74
202207	F	10040	999999	Company1	36721	Credit	551.62
202207	A	10040	999999	Company2	35403	Purchase	25000
202207	B	10040	999999	Company2	37961	Purchase	30000
202207	C	10040	999999	Company2	36386	Purchase	18000
202207	D	10040	999999	Company2	35719	Credit	12000
202207	E	10040	999999	Company2	37379	Purchase	436.79
202207	F	10040	999999	Company2	37964	Purchase	202.91
202207	A	10040	999999	Company2	36722	Purchase	140.29
202207	B	10040	999999	Company2	37773	Purchase	138.61
202207	C	10040	999999	Company2	36040	Purchase	129.14
202207	D	10040	999999	Company2	36042	Purchase	115.06
202207	E	10040	999999	Company2	37510	Purchase	109.84
202207	F	10040	999999	Company2	35890	Purchase	109.43
202207	A	10040	999999	Company2	38186	Purchase	99.79
202207	B	10040	999999	Company2	37774	Purchase	83.45
202207	C	10040	999999	Company2	36501	Purchase	82.36
202207	D	10040	999999	Company2	38349	Purchase	80.4
202207	E	10040	999999	Company2	36380	Purchase	75.79
202207	F	10040	999999	Company2	37960	Purchase	63.07
202207	A	10040	999999	Company2	36170	Purchase	49.38
202207	B	10040	999999	Company2	38453	Credit	45.72
202207	C	10040	999999	Company2	37509	Purchase	41.85
202207	D	10040	999999	Company2	38451	Purchase	31.27
202207	E	10040	999999	Company2	37149	Purchase	30.15
202207	F	10040	999999	Company2	37508	Purchase	27.62
202207	A	10040	999999	Company2	38626	Purchase	26.8
202207	B	10040	999999	Company2	38627	Purchase	24.13
202207	C	10040	999999	Company2	37025	Purchase	16.23
202207	D	10040	999999	Company2	38185	Purchase	15.34
202207	E	10040	999999	Company2	35401	Credit	14.52

I'm trying to get the grouped total of the transaction amounts but only when the amounts exceeds $10,000, and note how many of those transactions there are. This should occur for every customer in the old table and there are thousands. My new table formula is as follows:

New_Table =
GROUPBY (
'Old_Table',
'Old_Table'[SortDateCode],
'Old_Table'[Customer],
'Old_Table'[Type],
"Total", SUMx ( CURRENTGROUP (), IF('Old_Table'[TXAmount] > 10000, 'Old_Table'[TXAmount],0)),
"TXCount", COUNTAX ( CURRENTGROUP (), IF('Old_Table'[TXAmount] > 10000,'Old_Table'[TransactionID],0)
))

My result is :

202207 Company1 Purchase 47 $26914.75

202207 Company2 Purchase 29 $73000.00

202207 Company2 Credit 29 $12000.00

I can't seem to figure out how to show the accurate count of 2 instead of 47, 3 instead of 29 (Company 2 Purchase), and 1 instead of 29 (Company 2 Credit).

Thank for taking the time to look at this.

Re: Group By with filtered calculations

tamerj1 — Mon, 01 May 2023 16:35:15 GMT

Hi mjgenesis

please try

New_Table =
SUMMARIZE (
'Old_Table',
'Old_Table'[SortDateCode],
'Old_Table'[Customer],
'Old_Table'[Type],
"Total",
SUMX (
'Old_Table',
IF ( 'Old_Table'[TXAmount] > 10000, 'Old_Table'[TXAmount] )
),
"TXCount",
COUNTROWS (
DISTINCT (
SELECTCOLUMNS (
FILTER ( 'Old_Table', 'Old_Table'[TXAmount] > 10000 ),
"@TransactionID", 'Old_Table'[TransactionID]
)
)
)
)

Re: Group By with filtered calculations

mjgenesis — Sat, 06 May 2023 21:31:34 GMT

Thanks for this. It worked.

topic Re: Group By with filtered calculations in DAX Commands and Tips

Group By with filtered calculations

Re: Group By with filtered calculations

Re: Group By with filtered calculations