Forum Discussion

AbhinavJoshi's avatar
AbhinavJoshi
Icon for Responsive Resident rankResponsive 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 valu...
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! 🤠

AbhinavJoshi's avatar
AbhinavJoshi
Icon for Responsive Resident rankResponsive 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
Icon for Community Champion rankCommunity 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! 🤠