cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mhunt Frequent Visitor
Frequent Visitor

How to add these two columns?

Example.zip in dropbox

I'm working on a Production Dashboard in Desktop to track 2 production schedules: Production A (Jan-May) and Production B (July-Dec).

 

Production techs will fulfil orders using the Schedule Targets. They can fulfil these targets using existing inventory when available. If inventory doesn't exist in their spec, they'll have to create inventory.

 

Production techs will update their Delivery excel worksheet when they have completed a part in their spec for their production schedule. A part cannot be consumed in Schedule A and Schedule B.

 

The following excel charts are updated frequently to track production and delivery status:

  • Schedule Delivery
  • Inventory

The following excel chart is updated twice a year by a manager at the end of a project: 

  • Schedule A Targets
  • Schedule B Targets

The following excel charts are for reference only and do not need to be updated: 

  • Item Classifications

What I need help with:

I need to add 2 columns to the Production needed chart:

  1. Parts to Create (PTC)-
    If (Sum of design > Count of Part),Then PTC= Sum of design minus count of part.
    If (Sum of design <= Count of Part),Then PTC= 0
  2. Parts to Review (PTR)-
    If (Sum of design > Count of Part),Then PTR = count of part.
    If (Sum of design <= Count of Part),Then PTR = Sum of design

Example_screenshot.PNG

Is this possible with DAX, measures, or new columns?

1 ACCEPTED SOLUTION

Accepted Solutions
Nathaniel_C Super Contributor
Super Contributor

Re: How to add these two columns?

Hi @mhunt ,

Thank you for the clear explanation, and the pbix. Build a measure that is the sum, and another for the count, then:

PTC = IF([Sum of Design]>[Count of Part],([Sum of Design]) - ([Count of Part]),0)

PTR = IF([Sum of Design]>[Count of Part],[Count of Part],[Sum of Design])

Obviously these are for Schedule A
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

 

 

 

 

ptr.PNG

 

View solution in original post

2 REPLIES 2
Nathaniel_C Super Contributor
Super Contributor

Re: How to add these two columns?

Hi @mhunt ,

Thank you for the clear explanation, and the pbix. Build a measure that is the sum, and another for the count, then:

PTC = IF([Sum of Design]>[Count of Part],([Sum of Design]) - ([Count of Part]),0)

PTR = IF([Sum of Design]>[Count of Part],[Count of Part],[Sum of Design])

Obviously these are for Schedule A
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

 

 

 

 

ptr.PNG

 

View solution in original post

mhunt Frequent Visitor
Frequent Visitor

Re: How to add these two columns?

That's PERFECT! Thank you so much! And a fast response.

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.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 468 members 3,757 guests
Please welcome our newest community members: