Forum Discussion

flyingmada's avatar
flyingmada
Icon for Helper I rankHelper I
6 years ago
Solved

Summing Values from one Table based on Unique ID

I would like to sum multiple values from one table based on a unique ID and create a new table with this information. Then, I need to create a calculated column with the new information. Example is below.

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    flyingmada 

     

    New Table =
ADDCOLUMNS(
  SUMMARIZE(
    'Old Awful Disgustingly Bad Table',
    [ID],[Date],[Fruit],[Field],
    "Amount",SUM([Amount]),
    "Remaining",SUM([Remaining])
  ),
  "Amt/Rem",DIVIDE([Amount],[Remaining],0)
)

     

3 Replies

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi flyingmada 

    I have a slightly different solution to Greg_Deckler :

     

     

    New Table = 
SUMMARIZE (
    'Table',
    'Table'[ID],
    'Table'[Date],
    'Table'[Fruit],
    'Table'[Location],
    "Amount", SUM ( 'Table'[Amount] ),
    "Remaining", SUM ( 'Table'[Remaining] ),
    "Amt/Rem", DIVIDE ( SUM ( 'Table'[Amount] ), SUM ( 'Table'[Remaining] ) )
)

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    flyingmada 

     

    New Table =
ADDCOLUMNS(
  SUMMARIZE(
    'Old Awful Disgustingly Bad Table',
    [ID],[Date],[Fruit],[Field],
    "Amount",SUM([Amount]),
    "Remaining",SUM([Remaining])
  ),
  "Amt/Rem",DIVIDE([Amount],[Remaining],0)
)

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    To your visual, drag the first 4 fields to your Table visual and write these measures:

    Total amount = SUM(Data[Amount])

    Remaining amount = SUM(Data[Remaining])

    Amount/Rem = [Total amount]/[Remaining amount]

    Hope this helps.