cumulative count of grouped percentages without being filtered

[BusinessAnalyst]

Hi experts,

My data is as follow:

Each ID is an unique product.

Products was sent and its sending time is in column "Sending week", its receiving time is in column "Receiving week". Time is count by week number in a year. 

if column "Receiving week" is blank, it means " haven't received yet".

ID	Sending week	Receiving week
100	week 1
182	week 1	week 47
183	week 1	week 47
199	week 1
200	week 1
184	week 1	week 45
185	week 2	week 46
186	week 2	week 45
187	week 3	week 47
188	week 3	week 45
189	week 3	week 45
190	week 3	week 46
191	week 3	week 46
192	week 3	week 47

I want to create a column/measure in order to calculate grouped percentages of received products compared with the total number sent products. For example:

- total send of week 1 = count of all week 1 in column A = counta(week 1) = 5

- % receive up to week number 45 of send in week number 1 = count of column D (week 45)/count of column A(week1) = 1/5 = 20%

- % receive up to week number 47 of send in week number 1 = count of column D (week 47) + (week 45)/count of column A(week1) = (2 + 1)/5 = 60%

Desired output:

									Explanation
sending week		receiving week		count of receiving week		Percentage			count of receiving week (per sending week)	receiving percentage
week 1		week 45		1		20%			1(= week 45 appears once from "sending week" week 1)	20% because = count of "week 45"/count of sending week " week 1"= 1/5
week 1		week 47		2		60%			2 (=week 47 appears 2 times from "sending week" week 1)	60% because = count of "week 45" + count of "week 46" + count of "week 47" =  (1+0 +2)/5
week 2		week 45		1		50%			1	50% because =count of "week 45"/count of sending week"week 2"= 1/2
week 2		week 46		1		100%			1	100% because =count of "week 45" + count of "week 46"/count of sending week"week 2"= (1+ 1)/2
week 3		week 45		2		33%			2	33%  because =1/3
week 3		week 46		2		66%			2	66% because = (1+1)/3
week 3		week 47		2		100%			2	100% because = (1+1+1)/3

The problem is that once I filter a "receiving week", column "sending week" accordingly is filtered, which lead to the percentage of receiving week/sending week is always 100%- incorrect.

I would like to consult you and hopefully this issue can be solved.

Many thanks and regards,

Cindy

[v-haibl-msft]

@BusinessAnalyst

Please check if following formulas are what you want.

1. Create a column to store the receiving week number.
   

   Receiving week Num = 
   VALUE ( RIGHT ( Table1[Receiving week], 2 ) )

2. Create a measure to calculate the count of receiving week.
   

   count of receiving week = 
   COUNTA ( Table1[Receiving week] )

3. Create a measure to calculate the percentage.
   

   Percentage = 
   VAR Moving_Count_Of_Receiving_Week =
       CALCULATE (
           COUNTA ( Table1[Receiving week] ),
           FILTER (
               ALLEXCEPT ( Table1, Table1[Sending week] ),
               Table1[Receiving week Num] <= MAX ( Table1[Receiving week Num] )
           )
       )
   VAR All_Count_Of_Receiving_Week =
       CALCULATE (
           COUNTA ( Table1[Sending week] ),
           ALLEXCEPT ( Table1, Table1[Sending week] )
       )
   RETURN
   ( DIVIDE ( Moving_Count_Of_Receiving_Week, All_Count_Of_Receiving_Week ) )

Best Regards,

Herbert

[BusinessAnalyst]

Thank you very much for your great solution and your clear explanation. It was a huge help for me. 

In my sample data, receiving week values are only week 45, 47 for the products sent in week 1.

In reality, I need to check the cumulative percentage up to present time. For example now was week 49, I would like to see in week 49, how many percentage of products that was sent from week 1. Because there is no product coming in week 48 and week 49, so the cumulative percentage up to week 49 is still 50%.

Is that possilble to see that?

Thanks again for your contribution!

Have a nice day!
Cindy

[v-haibl-msft]

@BusinessAnalyst

I’ve received your PBIX file but I’m still a little confused about your requirement.

For the first one -- when I choose week 16 from "sending week", and filter Denmark from "country", the percentage should be 100%. Could you please explain the calculation method with the public sample data in your initial post?

For another wish, I also would like you to explain the calculation method with the public sample data. Sorry to trouble you…

Best Regards,

Herbert

[BhaveshPatel]

if you can provide a link to your data, I can provide you the exact solution.