Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Beyondforce
Helper I
Helper I

Calculating data from another table - Not Simple!!

‎05-27-2017 05:31 AM

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

 P1.png

 

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

 

p5.png

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.

Labels:
Message 1 of 6
848 Views
0
5 REPLIES 5
Phil_Seamark
Employee
Employee

‎05-27-2017 12:57 PM

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Message 2 of 6
803 Views
0
Beyondforce
Helper I
Helper I
In response to Phil_Seamark

‎05-27-2017 01:38 PM

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
Message 4 of 6
797 Views
0
Phil_Seamark
Employee
Employee
In response to Beyondforce

‎05-27-2017 02:19 PM

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?

 

bpd.png

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Message 5 of 6
791 Views
0
Beyondforce
Helper I
Helper I
In response to Phil_Seamark

‎05-27-2017 02:23 PM

Yes, that looks good 🙂

Message 6 of 6
787 Views
0
Beyondforce
Helper I
Helper I
In response to Phil_Seamark

‎05-27-2017 01:04 PM

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
Message 3 of 6
751 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All