Forum Discussion
Divide based on a grouped column
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
Anonymous , New column
new column =divide( [Number], countx(filter(Table, [zip code] =earlier([Zip Code]) && [Condition]= "Yes" ), [ID] ) )
5 Replies
Anonymous , New column
new column =divide( [Number], countx(filter(Table, [zip code] =earlier([Zip Code]) && [Condition]= "Yes" ), [ID] ) )
Hi Anonymous
I guess you are using table visual by ZipCode. Then you can use
Measure1 = DIVIDE ( SUM ( Table[Number] ), COUNTROWS ( FILTER ( Table, Table[Condition] = "Yes" ) ) )tamerj1 I edit the question
Anonymous
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 ) ) )
