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 Amount
20Apr  100

20 Apr  200

25 June 300
20Dec 500
Output: for date range selected 1st April to 30th June
Amount   600
. I have a table visual where there are many field.I do not want the date field in the output as it will change the grain of the data but want to aggregate for each date. Basically my measure should sum for each date but ignore all the fields present in the table visual

Output should be roughly of a window function in sql
SELECT ,SUM(AMOUNT) OVER (PARTIION BY DATE)
WHERE DATE > MIN(DATE1)--Selected start date
AND DATE <= MAX(DATE1) -- Selected end date

 

 

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.

3 Replies

  • 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

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    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
    Icon for Community Support rankCommunity Support
    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.