cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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).

 

Import_machining_res_transactions - Power BI Desktop_2018-11-21_11-46-08.png 

 

Thanks for any help!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: sumif construction

Hi @Hyperchef1969

Assume your data table(according to your screenshot)  is like

17.png

 

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])))

18.png

Best Regards

Maggie

View solution in original post

4 REPLIES 4
Highlighted
Stachu Super Contributor
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
Community Support Team

Re: sumif construction

Hi @Hyperchef1969

Assume your data table(according to your screenshot)  is like

17.png

 

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])))

18.png

Best Regards

Maggie

View solution in original post

Re: sumif construction

Thanks Maggie. Problem solved!

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)

 

Import_machining_res_transactions - Power BI Desktop_2018-11-22_14-24-35.png

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 6 members 878 guests
Please welcome our newest community members: