Calculate Percentage of Total count

[sathyaganapathi]

Hi,

I have two tables.  

1st one contains overall data.   I will consider the count of ID  from this table for the Denominator.

2nd table contains data of specific problem.  The count of problem will be the neumerator.

In both the table data is stored on daily basis. 

Percentage of N need to be calculated for every month Like below.  I could do this with below formula.

percentage = COUNT(Table2[ProblemCount]) / COUNT(Table1[ID])

Year	Month	Count of ID	ProblemCount	percentage
2022	January	532	31	5.83%
2022	February	594	43	7.24%
2022	March	792	73	9.22%
2022	April	841	80	9.51%
2022	May	617	59	9.38%
2022	June	641	75	11.70%
2022	July	594	56	9.35%
2022	August	580	56	9.57%
2022	September	624	50	7.89%
2022	October	617	58	9.40%
2022	November	40	2	5.00%

When drill down the month to next level (Dept), the calculation of Percentage should be based on the month total as Denominator.

Example for October 2022.  Denominator is 617.

Dept	ProblemCount	percentage
Copper	27	4.38%
Drilling	13	2.11%
Photo	4	0.65%
Relam	5	0.81%
SF	2	0.32%
SM	7	1.13%

Could somebody please help?

Thanks in advance.

[jennratten]

You're very welcome.  If this answered your question or helped please consider liking the reply and/or accepting it as a solution.

[jennratten]

Hello - in order for this percentage to be handled properly, in your measure you will have to evaluate whether or not various levels are visible using ISINSCOPE.  This is explained thoroughly with examples using SQLBI's hierarchy and percent of parent node patterns.  Please see this post.

https://www.daxpatterns.com/hierarchies/ 

[sathyaganapathi]

Hi jennratten,

I will go through it and in case of doubts I shall get back..

Thank you for quick inputs.. 🙂

[Anonymous]

Hi @sathyaganapathi ,

Sorry to bother you, is your problem solved?
If it is solved, please mark the helpful replie as a solution, if not please provide sample data for two tables and expected results, I will try to help.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sathyaganapathi]

Hi Stephen,

below is the output I am looking through drill down.

Year	Count of OrderNo	Hold4Count	percentage
2021	1243	108	8.69%
2022	6522	595	9.12%

Year	Month	Count of OrderNo	Hold4Count	percentage
2022	January	532	31	5.83%
2022	February	594	43	7.24%
2022	March	792	73	9.22%
2022	April	841	80	9.51%
2022	May	617	59	9.56%
2022	June	641	75	11.70%
2022	July	594	56	9.43%
2022	August	580	56	9.66%
2022	September	625	50	8.00%
2022	October	617	58	9.40%
2022	November	89	14	15.73%
		6522	595	9.12%

Jan-22
Dept	Count of Order No.	Hold4Count	percentage
Copper	532	19	3.57%
Drilling	532	3	0.56%
Photo	532	1	0.19%
Relam	532	3	0.56%
Route	532	1	0.19%
SF	532	2	0.38%
SM	532	1	0.19%
Tech	532	1	0.19%
	532	31	5.83%

Please let me know if you need more info from me..

Also, please let me know how I can send the excel files?

Thank you..

[Anonymous]

Hi @sathyaganapathi ,

I hope you can provide some of the original data of your two tables. Because I don't know the quantitative correspondence between your ID and department. You can copy and paste into the reply as your previous reply. Remember that this is a public forum and protect your privacy.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sathyaganapathi]

Hi Stephen Tao,

Please see the example data.  It is only few lines of data from the original table. 

The ID numbers is common on both the table. 

Table 1

ID	InputDateTime	FileName	DateCode	OrderNo	LotClearBy	LotClearTime	CycleTime	Hold1
176	10/28/21 10:11	ZEA7052	4321	600002299444	Halesha. V.	1/3/2022	67d 0h 47m	True
270	11/2/21 9:45	BE2229	432129-2ND T	606000026547	Akshay.N	12/20/2021	48d 1h 1m	True
279	11/3/21 9:01	IFM3140	4221	605000273024	Halesha. V.	11/19/2021	15d 14h 59m	True
285	11/3/21 18:44	RAI2201	4221	605000273130	Ramesh M B	11/15/2021	11d 22h 27m	True
287	11/4/21 9:35	PA9713	4421	600002306637	Srinivas K M	11/7/2021	3d 4h 7m	True
291	11/4/21 13:15	BE2244	432130	606000026602	Akshay.N	11/30/2021	25d 22h 41m	True
320	11/7/21 13:34	IFM0943	4421	600002305373	Halesha. V.	11/18/2021	10d 10h 26m	True
321	11/7/21 13:37	SAS4691	4421	600002294308	Srinivas K M	11/12/2021	5d 4h 31m	True
324	11/7/21 15:30	SAS4261	4421	600002296420	Srinivas K M	11/12/2021	5d 6h 53m	True
328	11/7/21 19:55	SMC0265	4421	605000272751	Raghavendra.G.V	12/13/2021	35d 14h 44m	True
330	11/7/21 21:51	HRO8961	4421	605000273957	Thrinesha Aradhya	12/20/2021	42d 13h 38m	True
336	11/8/21 12:24	SAS6890	4421	600002302308	Srinivas K M	11/12/2021	4d 10h 8m	True
344	11/8/21 21:48	SAS3703	4421	600002289048	Srinivas K M	11/12/2021	3d 19h 54m	True
362	11/9/21 21:22	SAS4691	4521	600002294296	Srinivas K M	11/12/2021	2d 21h 8m	True
364	11/9/21 22:31	SAS4691	4521	600002304248	Thrinesha Aradhya	12/20/2021	40d 13h 11m	True
381	11/10/21 14:30	END0081	4421	605000273258	Thrinesha Aradhya	12/20/2021	39d 21h 18m	True
385	11/10/21 18:08	HLT0490	21303	606000026409	Srinivas K M	11/18/2021	7d 15h 30m	True
390	11/11/21 8:11	AAT7103	2144	600002309295	Halesha. V.	12/2/2021	21d 1h 27m	True
391	11/11/21 8:20	SFU2751	4421-2ND T	605000273864	Halesha. V.	11/19/2021	8d 5h 25m	True
399	11/11/21 16:09	API0053	4321	606000026563	Raghavendra.G.V	12/21/2021	39d 22h 57m	True
401	11/11/21 17:32	ESP6601	4321	606000026553	Halesha. V.	12/22/2021	40d 18h 30m	True

Table 2:

ID

InputDateTime

FileName

DateCode

OrderNo

HoldStatus

Hold Date

Department

Reason

Reason Remarks

Action By

BlockedAt

Hold Status

HoldCT

CAPA Status

CAPA CT

Week

Month

Release Date

CAPA Date

13

########

ZEA0448

4221

600002289150

2142

202110

29

########

ZEA0252

4221

600002295569

2142

202110

135

########

ZEA8563

VCP-6X

600002293396

2143

202110

136

########

ZEA8563

VCP-6X

600002277333

2143

202110

176

########

ZEA7052

4321

600002299444

Released

########

Copper

Less PTH

Less PTH in COMP(1.3mm) SPEC= MIN 25 ACTUAL-23.02

DDA/AH/NV

Plab

Released

7

CAPAReceived

7

2143

202110

########

11/4/2021

279

########

IFM3140

4221

605000273024

Released

########

Copper

Less avg PTH

Less avg pth in via (0.35 mm) Spec Avg=20   Actual=19.62

DDA/AH/NV

Plab

Released

1

CAPAReceived

1

2144

202111

########

11/4/2021

285

########

RAI2201

4221

605000273130

Released

########

SM

More SM

More sm on epoxy  Spec Max 50  Actual = 52.69

TMK/MVM/KJS/SA

Plab

Released

2

CAPAReceived

8

2144

202111

########

11/12/2021

287

########

PA9713

4421

600002306637

Released

########

Copper

Less PTH & Surface Cu

Less Pth via (0.55 mm)  Spec=Min=25   Actual=19.30 Less Pth Comp (1.55 mm)  Spec Min=25 µm   Actual=21.57 Less surface cu   Spec=Min=60   Actual= 58.06

DDA/AH/NV

AQL

Released

2

CAPAReceived

6

2144

202111

########

11/10/2021

267

########

PIL1184

4221

600002298565

2144

202111

270

########

BE2229

432129-2ND T

606000026547

Released

########

Relam

More Dielc

spec=min 55 max 81 actual=100.540

VGM/KDN/NV

Plab

Released

6

CAPAReceived

6

2144

202111

########

11/8/2021

290

########

BE2228

422124 -REFREE

606000026473

2144

202111

291

########

BE2244

432130

606000026602

Released

########

Copper

More PTH

More pth in comp(1.10 mm)  Spec=Max=50  Actual =58.06

DDA/AH/NV

Plab

Released

5

CAPAReceived

6

2144

202111

########

11/10/2021

303

########

SAS4691

4421

600002294285

2144

202111

Hope this helps.  If not please let me know how I can send the excel files.

Thanks in advance..

Sathyaganapathi.

[sathyaganapathi]

Hi Stephen Tao,

Sorry for the delay in replying..  I was in other training for few days and could not touch this. 

Here I want to send below two files.   

1. FinalInputLogFor%Calculation.xlsx - the overall table.

2. FinalInputLogDeptFor%Calculation.xlsx - contains Dept info related to each ID in 1st file.

Can you please help me to send this files to you?  by mail? or any other way?

Please let me know if you need more info from me.

Thanks in advance for your support.

Sathyaganapathi.