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
Anonymous
Not applicable

Replacement Plan. Add value of table to filtered count of rows of another table

Hi! 

I´ve been struggling alot lately with a replacement plan.

 

Im working on a replacement plan report for welding machines at work, what I have is a list of all welding machines we have in all plants, with the machines status ( Current, Transitioning, Obsolete). What we want is to make a replacement plan so we can keep up with the machines changing status. So I have 3 tables, the one I meantioned earlier with all the welding machines, I have a second one with the machines model status over the years and a third one where i put how many new machines we are going to buy per plant.

 

So what i want to do, is add the number of new machines to the current count and substract it from the obsolete ones and in case there are no more obsoletes starts substracting from de transitioning. 

 

I´ll Put some examples of tables.

 

Table 1

PlantModel
PTModel A
STModel A
STModel A
FMModel C
PTModel B
PTModel C
STModel A
FMModel B
PTModel C
FMModel A
STModel B

 

Table 2

Model201920202021202220232024
Model ACurrentCurrentCurrentCurrentTransitioningTransitioning
Model BCurrentTransitioningTransitioningTransitioningObsoleteObsolete
Model CTransitioningTransitioningObsoleteObsoleteObsoleteObsolete

 

Table 3

Plant201920202021202220232024
PT120011
ST111201
FM110001

 

What I´ve done so far is merging Table 1 and Table 2 and then unpivoting the year. I´ve tried alot of things but none seem to work.

What i want is to do a scatter chart with the number of current, transitioning and obsolete machines over the years, I dont care about the models, just the numbers.

 

What I´ve been trying is getting the row count filtered by year and plant and add the number o new machines from Table 3 but I´ve been unable to do it since im new and i dont know very much.

 

If anyone has any idea how to tackle this problem i would be really grateful

8 REPLIES 8
Nathaniel_C
Super User
Super User

Hi @Anonymous ,

Could we read your tables as follows? Just trying to get a handle on them.
Plant PT currently has 4 machines, A,B, and 2 Cs. Plant PT will buy 5 machines over the time period.  3 machines will go obsolete, and 1 will transition, so at the end of the time period, we will have 3 extra machines?
Thanks,

Nathaniel

 

PlantPTModels 
  ABC 
Current???1124
20191   5
20202   7
20210  ob5
20220   5
20231 ob 4
20241tr  3




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Yes, we will have extra machines

I left out two tr. So for PT is this how you would read each year? 

PlantPTModels 
  ABC 
Current???1124
20191  tr4
20202 tr 5
20210  ob4
20220   4
20231 ob 4
20241tr  3




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

image.png

 

This is how i see your table, i cant understand it

I will work with yours.  I will be offline for awhile.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Nathaniel_C
Super User
Super User

Hi @Anonymous ,

Would it be possible to get a table like you have given us, but with your expected output.  You should be able to dummy it up in Excel.

Thank you,

Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Sure, First I need something like this

PlantStatusYearCountW/ Replacement Plan
PTCurrent201923
PTTransitioning201921
PTObsolete201900
PTCurrent202024
PTTransitioning202031
PTObsolete202000
PTCurrent202144
PTTransitioning202100
PTObsolete202111
STCurrent201945
STTransitioning201900
STObsolete201900
STCurrent202045
STTransitioning202010
STObsolete202000

 

Then In my scatter chart i need something like this

 

StatusYearCount
Current20198
Transitioning20191
Obsolete20190
Current20209
Transitioning20201
Obsolete20200

Thank you. A picture is  worth a thousand words!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.