Forum Discussion

Beyondforce's avatar
Beyondforce
Helper I
9 years ago

Calculating data from another table - Not Simple!!

Hey There,

 

So here it goes:

1. I have a table called "WeekData" (the first image)

2. The data in the "WeekData" comes from a dump CSV file - So far so good!

3. Check out the first image the data looks like...

 

 

4. I need to create a new table "Batch_Complition", collect and calculate the data from the "WeekData" table, like this:

 

5. I need to take out the Year and the Months, each on a seperate column like in the picture.

6. Count how many days were in ech particular month.

7. Count how many Batches were in each month (there are running several Batches  every day and each Batch has it's own row with the same date). The number of Batches can change, so it's better to count the recurring date number (3.marts 2017 x 10).

8. Count how many Batches were LateStart & LateEnd (we can just use the "Status" column for that. If a Batch was late the number is bigger than 0).

9. The "Total" column (sec. image) is the total Batches that were in a perticular month Minus the total number of Late Batch.

10. Then in the "Total%", I'm calculating the "Total" / ("Days" * "Blocks per day"). Then I get a percentage!

11. Last but not list, I will manually create a KPI column with 98% in it (That's easy!).

 

I hope it make sense to anyone and willing to take on that challenge :)

 

Thanks.

Need Help

5 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi Beyondforce

     

    Looks do-able.  Any chance you can post some sample data in the form of text and not an image so we can use it to build a prototype model?  This is just to save lots of typing in. :)

    • Beyondforce's avatar
      Beyondforce
      Helper I
      9 years ago

      Sure Phil_Seamark, here you go:

       

      WeekNumberTransactionDateBlockDateBlockNameLateStartLateEndOVERTRÆKMonth1StatusMonthDay
      91. marts 20171. marts 2017Batch 4000Nmarts0March01
      91. marts 20171. marts 2017Batch 6000Nmarts0March01
      91. marts 20172. marts 2017Batch 1000Nmarts0March01
      91. marts 20171. marts 2017Batch 2000Nmarts0March01
      91. marts 20171. marts 2017Batch 3000Nmarts0March01
      91. marts 20171. marts 2017Batch 3500Nmarts0March01
      91. marts 20171. marts 2017Batch 3300Nmarts0March01
      91. marts 20171. marts 2017Batch 6500Nmarts0March01
      91. marts 20171. marts 2017Batch 4500Nmarts0March01
      91. marts 20171. marts 2017Batch 5000Nmarts0March01
      92. marts 20172. marts 2017Batch 5000Nmarts0March02
      92. marts 20173. marts 2017Batch 1000Nmarts0March02
      92. marts 20172. marts 2017Batch 6500Nmarts0March02
      92. marts 20172. marts 2017Batch 4500Nmarts0March02
      92. marts 20172. marts 2017Batch 3500Nmarts0March02
      92. marts 20172. marts 2017Batch 6000Nmarts0March02
      92. marts 20172. marts 2017Batch 4000Nmarts0March02
      92. marts 20172. marts 2017Batch 3300Nmarts0March02
      93. marts 20173. marts 2017Batch 3500Nmarts0March03
      93. marts 20173. marts 2017Batch 6000Nmarts0March03
      93. marts 20173. marts 2017Batch 4000Nmarts0March03
      93. marts 20173. marts 2017Batch 3300Nmarts0March03
      93. marts 20173. marts 2017Batch 5000Nmarts0March03
      93. marts 20173. marts 2017Batch 2000Nmarts0March03
      93. marts 20173. marts 2017Batch 3000Nmarts0March03
      93. marts 20173. marts 2017Batch 6500Nmarts0March03
      93. marts 20173. marts 2017Batch 4500Nmarts0March03
      103. marts 20176. marts 2017Batch 1000Nmarts0March03
      106. marts 20176. marts 2017Batch 3000Nmarts0March06
      106. marts 20176. marts 2017Batch 4000Nmarts0March06
      106. marts 20176. marts 2017Batch 6000Nmarts0March06
      106. marts 20176. marts 2017Batch 5000Nmarts0March06
      106. marts 20177. marts 2017Batch 1000Nmarts0March06
      106. marts 20176. marts 2017Batch 3300Nmarts0March06
      106. marts 20176. marts 2017Batch 3500Nmarts0March06
      107. marts 20177. marts 2017Batch 2000Nmarts0March07
      107. marts 20177. marts 2017Batch 3000Nmarts0March07
      107. marts 20177. marts 2017Batch 4000Nmarts0March07
      107. marts 20177. marts 2017Batch 6000Nmarts0March07
      107. marts 20177. marts 2017Batch 4500Nmarts0March07
      107. marts 20177. marts 2017Batch 5000Nmarts0March07
      107. marts 20177. marts 2017Batch 6500Nmarts0March07
      107. marts 20177. marts 2017Batch 3300Nmarts0March07
      107. marts 20177. marts 2017Batch 3500Nmarts0March07
      108. marts 20178. marts 2017Batch 7000Nmarts0March08
      108. marts 20178. marts 2017Batch 6000Nmarts0March08
      108. marts 20178. marts 2017Batch 3000Nmarts0March08
      108. marts 20178. marts 2017Batch 5000Nmarts0March08
    • Beyondforce's avatar
      Beyondforce
      Helper I
      9 years ago

      Here you go Phil_Seamark

       

       

       

      WeekNumber	TransactionDate	BlockDate	BlockName	LateStart	LateEnd	OVERTRÆK	Month1	Status	Month	Day
9	1. marts 2017	1. marts 2017	Batch 40	0	0	N	marts	0	March	01
9	1. marts 2017	1. marts 2017	Batch 60	0	0	N	marts	0	March	01
9	1. marts 2017	2. marts 2017	Batch 10	0	0	N	marts	0	March	01
9	1. marts 2017	1. marts 2017	Batch 20	0	0	N	marts	0	March	01
9	1. marts 2017	1. marts 2017	Batch 30	0	0	N	marts	0	March	01
9	1. marts 2017	1. marts 2017	Batch 35	0	0	N	marts	0	March	01
9	1. marts 2017	1. marts 2017	Batch 33	0	0	N	marts	0	March	01
9	1. marts 2017	1. marts 2017	Batch 65	0	0	N	marts	0	March	01
9	1. marts 2017	1. marts 2017	Batch 45	0	0	N	marts	0	March	01
9	1. marts 2017	1. marts 2017	Batch 50	0	0	N	marts	0	March	01
9	2. marts 2017	2. marts 2017	Batch 50	0	0	N	marts	0	March	02
9	2. marts 2017	3. marts 2017	Batch 10	0	0	N	marts	0	March	02
9	2. marts 2017	2. marts 2017	Batch 65	0	0	N	marts	0	March	02
9	2. marts 2017	2. marts 2017	Batch 45	0	0	N	marts	0	March	02
9	2. marts 2017	2. marts 2017	Batch 35	0	0	N	marts	0	March	02
9	2. marts 2017	2. marts 2017	Batch 60	0	0	N	marts	0	March	02
9	2. marts 2017	2. marts 2017	Batch 40	0	0	N	marts	0	March	02
9	2. marts 2017	2. marts 2017	Batch 33	0	0	N	marts	0	March	02
9	3. marts 2017	3. marts 2017	Batch 35	0	0	N	marts	0	March	03
9	3. marts 2017	3. marts 2017	Batch 60	0	0	N	marts	0	March	03
9	3. marts 2017	3. marts 2017	Batch 40	0	0	N	marts	0	March	03
9	3. marts 2017	3. marts 2017	Batch 33	0	0	N	marts	0	March	03
9	3. marts 2017	3. marts 2017	Batch 50	0	0	N	marts	0	March	03
9	3. marts 2017	3. marts 2017	Batch 20	0	0	N	marts	0	March	03
9	3. marts 2017	3. marts 2017	Batch 30	0	0	N	marts	0	March	03
9	3. marts 2017	3. marts 2017	Batch 65	0	0	N	marts	0	March	03
9	3. marts 2017	3. marts 2017	Batch 45	0	0	N	marts	0	March	03
10	3. marts 2017	6. marts 2017	Batch 10	0	0	N	marts	0	March	03
10	6. marts 2017	6. marts 2017	Batch 30	0	0	N	marts	0	March	06
10	6. marts 2017	6. marts 2017	Batch 40	0	0	N	marts	0	March	06
10	6. marts 2017	6. marts 2017	Batch 60	0	0	N	marts	0	March	06
10	6. marts 2017	6. marts 2017	Batch 50	0	0	N	marts	0	March	06
10	6. marts 2017	7. marts 2017	Batch 10	0	0	N	marts	0	March	06
10	6. marts 2017	6. marts 2017	Batch 33	0	0	N	marts	0	March	06
10	6. marts 2017	6. marts 2017	Batch 35	0	0	N	marts	0	March	06
10	7. marts 2017	7. marts 2017	Batch 20	0	0	N	marts	0	March	07
10	7. marts 2017	7. marts 2017	Batch 30	0	0	N	marts	0	March	07
10	7. marts 2017	7. marts 2017	Batch 40	0	0	N	marts	0	March	07
10	7. marts 2017	7. marts 2017	Batch 60	0	0	N	marts	0	March	07
10	7. marts 2017	7. marts 2017	Batch 45	0	0	N	marts	0	March	07
10	7. marts 2017	7. marts 2017	Batch 50	0	0	N	marts	0	March	07
10	7. marts 2017	7. marts 2017	Batch 65	0	0	N	marts	0	March	07
10	7. marts 2017	7. marts 2017	Batch 33	0	0	N	marts	0	March	07
10	7. marts 2017	7. marts 2017	Batch 35	0	0	N	marts	0	March	07
10	8. marts 2017	8. marts 2017	Batch 70	0	0	N	marts	0	March	08
10	8. marts 2017	8. marts 2017	Batch 60	0	0	N	marts	0	March	08
10	8. marts 2017	8. marts 2017	Batch 30	0	0	N	marts	0	March	08
10	8. marts 2017	8. marts 2017	Batch 50	0	0	N	marts	0	March	08
      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        Hi Beyondforce

         

        To start with the simple ones,  Can we just add the following measures and they will cover your first columns?

         

        Days = DISTINCTCOUNT('Table1'[Day])

Batches per day = COUNT('Table1'[B

        and based on your sample data, should this be the correct result for these two?