Sum column values based on slicers

[amre]

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!

[v-yangliu-msft]

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]

Hello @v-yangliu-msft,

 

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]

@amre Can you share sample output in table format? 

[amre]

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.