Forum Discussion

dolevh's avatar
dolevh
Helper II
4 years ago
Solved

Sum Measure with Filter

Hi, 

I'm looking for Dax that show me the Total Count
if (FirstDayofMonth = FirstDayofMonth and AreaID = AreaID and CityCode = CityCode and HoodID = HoodID).

 

My name of this table is: NadRe 

 

FirstDayofMonth   CustID AreaID  CityCode HoodID Count
1/1/2111111  120010010
1/1/2122222  120010020
1/1/2133333  120010030
1/1/2177777  240010100
1/1/2166666  240010200
2/1/2111111  12001001000
2/1/2122222  12001002000
2/1/2133333  12001003000
2/1/2177777  24001010,000
2/1/2166666  24001020,000

 

This is the result:  

FirstDayofMonth  HoodID  Count
1/1/21  100      60
1/1/21  10     300
2/1/21  100  6,000
2/1/21  1030,000

 

Thanks All !

Need Help DAX
  • smpa01's avatar
    smpa01
    4 years ago

    dolevh  you can simply use this measure

    Measure =
CALCULATE (
    SUM ( 'Table'[Count] ),
    ALLEXCEPT ( 'Table', 'Table'[FirstDayofMonth], 'Table'[HoodID] )
)

     

     

3 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    Hi,

    This shoudl do what you want: 

    Table 2 = SUMMARIZE('Summarize (2)','Summarize (2)'[FirstDayofMonth ],'Summarize (2)'[ HoodID],"Count",SUM('Summarize (2)'[ Count]))


    I hope this helps and if it does consider accepting this as a solution!

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    dolevh  you can simply use this measure

    Measure =
CALCULATE (
    SUM ( 'Table'[Count] ),
    ALLEXCEPT ( 'Table', 'Table'[FirstDayofMonth], 'Table'[HoodID] )
)

     

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new table, and it uses SUMMARIZECOLUMNS function.

     

     

    Result Table =
    SUMMARIZECOLUMNS (
    NadRe[FirstDayofMonth],
    NadRe[HoodID],
    "Count", SUM ( NadRe[Count] )
    )