Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
Division
SUMMARIZE
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , New column

     

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

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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