topic Re: Divide based on a grouped column in DAX Commands and Tips

Divide based on a grouped column

Anonymous — Mon, 18 Apr 2022 17:18:30 GMT

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

Re: Divide based on a grouped column

amitchandak — Mon, 18 Apr 2022 11:21:15 GMT

Anonymous , New column

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

Re: Divide based on a grouped column

Anonymous — Mon, 18 Apr 2022 11:33:15 GMT

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

Re: Divide based on a grouped column

tamerj1 — Mon, 18 Apr 2022 16:36:17 GMT

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" ) ) )

Re: Divide based on a grouped column

Anonymous — Mon, 18 Apr 2022 17:18:58 GMT

tamerj1 I edit the question

Re: Divide based on a grouped column

tamerj1 — Mon, 18 Apr 2022 17:30:55 GMT

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