Measure: Summarize by Group and add and IF statement

pbiwizard — Mon, 13 Nov 2023 16:52:02 GMT

How to sum/summarize, by group and an IF:

Data As Is
Product Name	Transaction ID	Jurisdiction Type	Gross Amount
P0001	123123	STATE	125.00
P0001	123123	COUNTY	125.00
P0001	123123	CITY	125.00
P0001	123123	STC	125.00
P0001	123123	STATE	125.00
P0001	123123	CITY	125.00
P0001	123123	STATE	25.00
P0002	456456	STATE	10.00
P0002	456456	CITY	10.00
P0002	456456	STATE	10.01
P0003	456456	STATE	5.00
P0004	789789	STATE	1.50
P0004	789789	STATE	1.50

Summary of what it should be
Product Name	Transaction ID	Product Name	Total Gross Amount
P0001	123123	P0001	275.00
P0002	456456	Multiple/P0002/P0003	25.01
P0004	789789	P0004	3.00

Formula 1:

Gross Sales =

SUMX(

KEEPFILTERS(

VALUES(

'Transactions'[Product_Name]

)

CALCULATE(

SUMX(

SUMMARIZE('Transactions', 'Transactions'[Transaction ID], 'Transactions'[Gross Amount] ),

[Gross Amount])

)

This gives me Transaction 123123 for 150.00, but should be Transaction 123123 as 275.00 It returns the sum of non-duplicate values.

However, I would like for it to return the sum of values, even if it is a duplicate value, IF the Jurisdiction Type is "STATE" is present multiple times. The Jurisdiction Type as State represents a separate, product/purchase. For example, IF I go to the store, and I buy two avocados in one bag, and the cashier rings it up, and then scan more products, and then I have two more avodacos in the same purchase, the receipt will have 2 avocados for 3.00, then Cereal for 5.00, Soup for 2.00, and then 2 MORE avocados for 3.00. However, I want it to combine the avocados into 4 avocados for 6.00.

So I want to continue to group by the Product name and transaction ID, but add and IF the jurisdiction type is "STATE" then also sum/add, even if it is a duplicate value.

I also used the formula below but returns the same values as Formula 2.

FORMULA 2:

Gross Sales 1 =

SUMX(

FILTER(

'Transactions', [Jurisdiction Type] = "STATE"),

CALCULATE(

SUMX(

SUMMARIZE('Transactions', 'Transactions'[Transaction ID], 'Transactions'[Gross Amount]),

[Gross Amount])

)

Re: Measure: Summarize by Group and add and IF statement

AlexisOlson — Mon, 13 Nov 2023 17:05:16 GMT

I think the issue is including [Gross Amount] in the grouping (rather than aggregating it with a SUM).

If you want to include duplicate rows for STATE but not other jurisdictions, try this:

Gross Sales = VAR _Summary_ = SUMMARIZE ( 'Transactions', 'Transactions'[Product Name], 'Transactions'[Transaction ID], 'Transactions'[Jurisdiction Type], "@SumAmount", SUM ( 'Transactions'[Gross Amount] ), "@MaxAmount", MAX ( 'Transactions'[Gross Amount] ) ) RETURN SUMX ( _Summary, IF ( 'Transactions'[Jurisdiction Type] = "STATE", [@SumAmount], [@MaxAmount] ) )

If you always want to count duplicates (not just for STATE), then I think you just do a simple sum:

Gross Sales = SUM ( 'Transactions'[Gross Amount] )

topic Measure: Summarize by Group and add and IF statement in DAX Commands and Tips

Measure: Summarize by Group and add and IF statement

Re: Measure: Summarize by Group and add and IF statement