Dax Measure help - Sum amount where salesorder matches multiple productgroups

[Anonymous]

Hi there

I have a challenge I hope you can help out with.

I have a fact table containing sales orders,  which item was sold, sales amount, and which productgroup they are a part of.

What I need is two measures/column that;

- Finds all salesorders with the same SalesOrderID WHERE both productgroup XYZ and Productgroup YZX is sold. Return only sum of amount for the rows that match both productgroups and ignore any other item sold for that salesorder. 

- Finds all salesorders with the same SalesOrderID WHERE both productgroup XYZ and Productgroup YZX is sold. Return sum of all products for that salesorder. 

Example table:

SalesorderID	Item	ProductGroup	Amount
123	Box	1	5,00
568	Tape	3	3,50
123	Duct tape	8	2,50
568	Box	1	5,00
123	Tape	3	3,50

Wanted output if a salesorder has sold products from both productGroup 1 and 8 would be to match SalesOrderId 123 and return Amount 7,5 for first measure/column and Amount 11 for sum of all products for that specific salesorder.

Salesorder 568 would be ignored, as neither  of the ProductGroups matches the search.

I know I could accomplish this by creating a new table  using "SELECTCOLUMNS" and then do the measures, but the problem with this approach is that it blows up the dataamount too much and the pbix file exceeds 1gb. 

[Anonymous · Accepted solution]

Thanks for the help and answers, as it pointed me in the right direction.

What I ended up doing was the following;
1) Created a new calculated column that will sum amount if both of the product groups is in a sales order, and then divide it on the number of rows for that specific Salesorder;

BothItemGroups = 
VAR Group1 = SUMX(FILTER(SalesData, SalesData[SalesorderID] = EARLIER(SalesData[SalesorderID]) && (RELATED(Product[Group]) = 1 ) ),
SalesData[Amount])
VAR Group2 = SUMX(FILTER(SalesData, SalesData[SalesorderID] = EARLIER(SalesData[SalesorderID]) && (RELATED(Product[Group]) = 😎 ),
SalesData[Amount])

Var OrderCount = 
    CALCULATE (
        COUNT ( 'SalesData'[SalesorderID] ),
        ALLEXCEPT ( 'SalesData', 'SalesData'[SalesorderID] )
    )
Return
IF( Group1 <> 0 && Group2 <> 0, DIVIDE(Group1 + Group2,OrderCount), 0)

 
I then went ahead and created the measures I needed, as I've now been able to identify the number of distinct salesorder that matches those groups and as well can easily sum the amounts. 

[V-lianl-msft]

Hi @Anonymous ,

Refer to:

Measure = 
var in_both = CALCULATE(
    COUNTROWS(FILTER(ALLSELECTED('Table'),
        COUNTROWS(FILTER('Table',EARLIER('Table'[SalesorderID])='Table'[SalesorderID])))))
var order_id = CALCULATE(VALUES('Table'[SalesorderID]),FILTER('Table',in_both=2))
return CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[SalesorderID] = order_id))

Measure 2 = IF(ISBLANK([Measure]),BLANK(),CALCULATE(SUM('Table'[Amount]),ALL('Table')))

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

Where are you defining the productGroup to search for e.g. "productGroup 1 and 8"?

and what's the desired output?