Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Grouped sum on condition

Hi All,   I am facing a strange aggregation problem. I have an amount field which I need to filter for a date range selection and this amount field should sum for each date. E,g Input Data Date A...
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    Can you try the following measure? You can keep the date and other columns in the visual.

    Total = 
CALCULATE(
    SUM(Table[Amount]),
    ALLEXCEPT(Table,Table[Date])
)

     

    ________________________

    If my answer was helpful, please click Accept it as the solution to help other members find it useful

    Click on the Thumbs-Up icon if you like this reply 🙂


    Website YouTube  LinkedIn

  • v-henryk-mstf's avatar
    v-henryk-mstf
    5 years ago

    Hi Anonymous ,

     

    According to your request, you want to use DAX to implement the calculation logic of SQL formula in PBI. I did the following test to sum the value of the Amount column based on the Date.

     

     

    Best Regards,
    Henry

     

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , Looking at data

a measure like this should work 

calculate(Sum(Table[Date]))

 

Or PARTITION by Date

sumx(values(Table[Date]), calculate(Sum(Table[Date])))

 

An independent Date file
measure =
var _min = minx(allselected('Date'), 'Date'[Date])
var _max = maxx(allselected('Date'), 'Date'[Date])
return
calculate(sumx(values(Table[Date]), calculate(Sum(Table[Date]))), filter( Table, Table[Date] >=_min && Table[Date] <=_max))

 

Joined date filter

measure =
var _min = minx(allselected('Date'), 'Date'[Date])
var _max = maxx(allselected('Date'), 'Date'[Date])
return
calculate(sumx(values(Table[Date]), calculate(Sum(Table[Date]))), filter( Date, Date[Date] >=_min && Date[Date] <=_max)) // there is not need of date filter if joined