Forum Discussion

AbhinavJoshi's avatar
AbhinavJoshi
Responsive Resident
2 years ago
Solved

Group By with Conditions

Hi All, 

 

I have the following dataset, it has bunch of other columns. I would like to group the data by ID using Power Query. I would like to show the total of amount for the the higest year value in date column. For example: ID =1, Amount should be 80 (only including 2024 year). Also, I would like to see the highest date column with the total amount column. Another example: For ID = 2, total should be 150 (only including the 2023 year as it is the highest year for that ID) 

IDDateAmount
12023-01-09 20
12023-09-10 20
12024-01-17 40
12024-02-11 40
22023-01-09 100
32024-01-17 55
42024-01-17 65
22022-02-11 50
62023-12-11 20
62024-01-01 20
62024-02-01 20
22023-02-09 50

 

Let me know if something like this is possible. 

 

Thanks, 

Abhinav

Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    AbhinavJoshi 

    output 

     

     

     

    dax calculated table : 

    Table 2 = 

var ds = 
ADDCOLUMNS(
ADDCOLUMNS(
SUMMARIZE(
    Table4,
    Table4[ID]
),
   "max year" , CALCULATE(MAX(Table4[year]))
),
"total amount" , 
var maxyear =  [max year]
RETURN
CALCULATE(SUM(Table4[Amount]) ,  Table4[year]  =maxyear ))

return ds

     

     

    let me know if this helps .

     

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

  • Daniel29195's avatar
    Daniel29195
    2 years ago

    AbhinavJoshi 

    output 

    let
    Source = Table4,
    Grouped = Table.Group(Source, {"ID", "Year"}, {{"Total Amount", each List.Sum([Amount]), type number}}),
    MaxYear = Table.Group(Grouped, {"ID"}, {{"MaxYear", each List.Max([Year]), type number}}),
    Result = Table.Join(MaxYear, {"ID", "MaxYear"}, Grouped, {"ID", "Year"}),
    FinalResult = Table.SelectColumns(Result, {"ID", "Year", "Total Amount"})
in
    FinalResult

     

     

     

    let me know if this helps .

     

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

5 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    2 years ago

    its not really clear what you saying please provide your expected result.    i understand you want  the sum for the year but please provide the expected output as its hard to understand what you are expecting to actually see as the result output from your data

  • AbhinavJoshi's avatar
    AbhinavJoshi
    Responsive Resident
    2 years ago

    Please see expected result

    IDLatest Year Total Amount for Latest Year
    1202480
    22023150
    3202455
    4202465
    6202440

     

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    AbhinavJoshi 

    output 

     

     

     

    dax calculated table : 

    Table 2 = 

var ds = 
ADDCOLUMNS(
ADDCOLUMNS(
SUMMARIZE(
    Table4,
    Table4[ID]
),
   "max year" , CALCULATE(MAX(Table4[year]))
),
"total amount" , 
var maxyear =  [max year]
RETURN
CALCULATE(SUM(Table4[Amount]) ,  Table4[year]  =maxyear ))

return ds

     

     

    let me know if this helps .

     

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

    • AbhinavJoshi's avatar
      AbhinavJoshi
      Responsive Resident
      2 years ago

      Thank you Daniel29195. Would it be possible to achive the same in Power Query, the reason I'm asking because I'm doing a lot of other modelling there and have other columns that I would like to keep as well.

      • Daniel29195's avatar
        Daniel29195
        Community Champion
        2 years ago

        AbhinavJoshi 

        output 

        let
    Source = Table4,
    Grouped = Table.Group(Source, {"ID", "Year"}, {{"Total Amount", each List.Sum([Amount]), type number}}),
    MaxYear = Table.Group(Grouped, {"ID"}, {{"MaxYear", each List.Max([Year]), type number}}),
    Result = Table.Join(MaxYear, {"ID", "MaxYear"}, Grouped, {"ID", "Year"}),
    FinalResult = Table.SelectColumns(Result, {"ID", "Year", "Total Amount"})
in
    FinalResult

         

         

         

        let me know if this helps .

         

         

         

        If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
        It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠