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 (...
Need Help
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!

  • Mahesh0016's avatar
    Mahesh0016
    Super User
    3 years ago

    amre Can you share sample output in table format? 

    • amre's avatar
      amre
      Frequent Visitor
      3 years ago

      Hello Mahesh0016,

      I hope I am able to explain it in a more elaborated way. I need to select the unique value for the Total column sum if the user's present date is the same.