Divide based on a grouped column

[filipebodas]

Hello everyone,

I have a table with 5 columns [Date, ID, ZipCode, Numeric, Condition]

Date	ID	ZipCode	Number	Condition
27/03/2020	A	MTS	117525	Yes
27/03/2020	B	MTS	117525	Yes
19/03/2020	C	MTS	117525	Yes
25/02/2020	D	MTS	0	No
20/01/2020	E	FCM	120368	Yes
13/03/2020	F	FCM	120368	Yes
17/03/2020	G	FCM	0	No
05/04/2020	H	MTS	247831	Yes
31/03/2020	I	MTS	0	No
08/04/2020	J	MTS	247831	Yes

The goal is to divide the Number with the total count of "Yes".

• For ZipCode "MTS" I want to divide 117.525/3, being 3 the total number of Yes for that ZipCode and for that Number. The Number results from a condition based on the Date.
• For ZipCode "FCM" I want to divide 120.368/2.
• Again for MTS, 247.831/2
• For the rows where the Condition = "No" the result should be 0.

How do i lock that count for each row? 

Edit: I add another ZipCode to clarify the goal

[tamerj1]

@filipebodas 

Use this for a calculated column

Column1 =
VAR ZipCodeTable =
    CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[ZipCode] ) )
VAR YesTable =
    FILTER ( ZipCodeTable, Table[Condition] = "Yes" )
RETURN
    IF (
        Table[Condition],
        0,
        DIVIDE ( SUMX ( YesTable, Table[Number] ), COUNTROWS ( YesTable ) )
    )

[tamerj1]

Hi @filipebodas 

I guess you are using table visual by ZipCode. Then you can use

Measure1 =
DIVIDE (
    SUM ( Table[Number] ),
    COUNTROWS ( FILTER ( Table, Table[Condition] = "Yes" ) )
)

[filipebodas]

@tamerj1 I edit the question

[amitchandak · Accepted solution]

@filipebodas , New column

new column =divide( [Number], countx(filter(Table, [zip code] =earlier([Zip Code]) && [Condition]= "Yes" ), [ID] ) )

[filipebodas]

When the condition is "No" the new column should be 0.

The output should be 39175 | 39175 | 39175 | 0 (these are rows). 

The zipcode can change along the table