cancel
Showing results for
Did you mean:
Hyperchef1969 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

Assume your data table(according to your screenshot)  is like 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
Stachu 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] ) )
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Community Support Team

Re: sumif construction

Assume your data table(according to your screenshot)  is like 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

Hyperchef1969 Member

Re: sumif construction

Thanks Maggie. Problem solved!

Hyperchef1969 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. 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:
• amitchandak • SathyaG35 • caytung09 • mohanyeedhi • johnmelbourne • smpa01 • Ngene • troystaylor • mexicobigdata • Bielite • kylerligon • drewsk • Ashley_Finney 