Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hyperchef1969
Helper V
Helper V

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
v-juanli-msft
Community Support
Community Support

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
v-juanli-msft
Community Support
Community Support

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

Thanks Maggie. Problem solved!

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

Stachu
Community Champion
Community Champion

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!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.