cancel
Showing results for
Did you mean:
Member

## sumif construction

Hi,

For each 'MFG_ORDER_NAME' and 'OP_SEQ_NUMBER' and I need to calculatie the sum of 'RESOURCE_REQUIRED_AMOUNT' and sum of transaction quantity. The last one is easy and I do not need help with that.

The problem lies within the resource required amount. If 'RESOURCE_SEQ_NUMBER'= 20 AND 'BASIS' is "Lot", the sum within this example should be 160. For each transaction quantity row, the underlying table shows 160. If 'RESOURCE_SEQ_NUMBER' = 30 AND 'BASIS' is "Item", the sum should be the 810. This is the line qty of 270 * Order qty based in the table JOBS (linked but not shown here).

Thanks for any help!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: sumif construction

If the table JOBS is like this, and there a relationship built between two tables (it is better to set cross filter direction "Both")

 ORDER_NAME qty 1 3 2 4 3 5 4 6 5 7

Then create a measure

`Measure = IF(MAX([col4])=20&&MAX([col5])="Lot",MAX([col6]),IF(MAX([col4])=30&&MAX([col5])="Item",MAX([col6])*MAX(Sheet12[qty])))`

Best Regards

Maggie

4 REPLIES 4
Highlighted
Super Contributor

## Re: sumif construction

most likely you need to use SUMX with RELATED
https://docs.microsoft.com/en-us/dax/sumx-function-dax
https://docs.microsoft.com/en-us/dax/related-function-dax

Something like

`Measure =SUMX ( Table, [RESOURCE_REQUIRED_AMOUNT] * RELATED ( JOBS[Quantity] ) )`

Proud to be a Datanaut!

Community Support Team

## Re: sumif construction

If the table JOBS is like this, and there a relationship built between two tables (it is better to set cross filter direction "Both")

 ORDER_NAME qty 1 3 2 4 3 5 4 6 5 7

Then create a measure

`Measure = IF(MAX([col4])=20&&MAX([col5])="Lot",MAX([col6]),IF(MAX([col4])=30&&MAX([col5])="Item",MAX([col6])*MAX(Sheet12[qty])))`

Best Regards

Maggie

Member

## Re: sumif construction

Thanks Maggie. Problem solved!

Member

## Re: sumif construction

Now the individual lines are correct but the sum of 'RESOURCE_REQUIRED_AMOUNT' is incorrect, which now is 13.5

This should be 32.85. I used following calculation.

Hrs_routing = IF(MAX(XXBI_MFG_RESOURCE_TXNS_V[RESOURCE_SEQUENCE_NUMBER])=20 && MAX(XXBI_MFG_RESOURCE_TXNS_V[BASIS])="Lot",max(XXBI_MFG_RESOURCE_TXNS_V[RESOURCE_REQUIRED_AMOUNT])/60,(MAX(XXBI_WIP_JOBS_V[QUANTITY_COMPLETED])*MAX(XXBI_MFG_RESOURCE_TXNS_V[RESOURCE_REQUIRED_AMOUNT]))/60)

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 6 members 878 guests
Recent signins: