Forum Discussion

amre's avatar
amre
Frequent Visitor
3 years ago

Sum column values based on slicers

Hi all,

 

I am trying to create a mesure where I need to sum only the column (Total) values which are based on the slicer filters. There are two slicers: ID (from the fact table) and a Date range (a dimension table). Secondly, if there is no option selected from the slicer then all values should be summed up.

I have tried to use SUMX(DISTINCT(DatassetFact[Total]), DatassetFact[Total]) which is working fine for the distinct values but we need to sum all values that are filtered based on ID and Date range. Whereas Sum(DatassetFact[Total]) provide sum of all the values in the column not based on the slicer selection.

 

 

I also tried with Filters() with ALLSELECTED() but didn’t get the required results.

 

Thanks in advance!

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  amre ,

     

    Here are the steps you can follow：

    1. Create calculated table.

    Date_Slicer =
DISTINCT('Table'[Date])

    ID_Slicer =
DISTINCT('Table'[ID])

    2. Create measure.

    Sum_value =
var _selectID=SELECTCOLUMNS('ID_Slicer',"ID",[ID])
var _minDate=MINX(ALLSELECTED('Date_Slicer'),[Date range])
var _maxDate=MAXX(ALLSELECTED('Date_Slicer'),[Date range])
return
SUMX(
    FILTER(ALLSELECTED('Table'),
    'Table'[ID] in _selectID && 'Table'[Date] >=_minDate&&'Table'[Date]<= _maxDate),[Total])

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • amre's avatar
      amre
      Frequent Visitor
      3 years ago

      Hello Anonymous,

       

      Thank you, your purposed method is fulfilling the requirement but what if same user have repeated totals for the same date then in case only one TOTAL value should be consider to sum.

       

      for example, for user having ID =6 and 12 have data like below where "...." represents some other columns having unique values :

       6  A  7.12.2022  320 ... ... ... ... .. .. .. ..

       6  A  7.12.2022  320 ... ... ... ... .. .. .. ..

       12  C  7.12.2022  400 ... ... ... ... .. .. .. ..

       12  C  7.12.2022  400 ... ... ... ... .. .. .. ..

      Thanks!