Forum Discussion

adityah's avatar
adityah
Frequent Visitor
3 years ago
Solved

How to summarize a table correctly

Hi,

 

Please excuse if the question seems trivial, I am a newbie and have tried various solutions which havent worked. I am trying to summarize a table 'Budget' which looks like this:

 

RegionPower BIUSDTypeCat.
xxNon-Motor12500ActualCAPEX
xxNon-Motor510.51ActualOPEX
xxNon-Motor2473.671ActualOPEX
xxNon-Motor757.4359ActualOPEX
yyNon-Motor680.5167ActualOPEX
yyNon-Motor1118.632ActualCAPEX
yyMotor6992ActualCAPEX
yyMotor4736.987ActualCAPEX
yyMotor757.4359ActualOPEX
yyMotor1740ActualCAPEX
yyMotor2967.308ActualCAPEX
xxMotor2294.103ActualCAPEX
xxMotor3191.795ActualCAPEX

 

to look like this:

 

RegionPower BIActualCat.
xxNon-Motor12500CAPEX
xxNon-Motor3741.616OPEX
yyNon-Motor680.5167OPEX
yyNon-Motor1118.632CAPEX
yyMotor16436.29CAPEX
yyMotor757.4359OPEX
xxMotor2294.103CAPEX

 

I don't actually need the column 'Type' and instead replace the USD column name with 'Actual'

  • amitchandak's avatar
    amitchandak
    3 years ago

    adityah , a sum(Table[USD]) should work in table visual with all other columns

     

     

    If you need a data table,

     

    Summarize(Table, Table[Region], Table[Power BI], Table[Cat], "Actual", SUm(table[USD]))

     

    Learn Power BI: Calculatetable, Summarize, Groupby, SummarizeColumns, Except, Distinct, Generateseries, Generate, Crossjoin
    https://youtu.be/cN8AO3_vmlY?t=22350

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi adityah 

    try to add a calculated table like:

    Table = 
ADDCOLUMNS(
    SUMMARIZE(
        budget,
        budget[Region],
        budget[Power BI],
        budget[Cat.]
    ),
    "Actual",
    CALCULATE(SUM(budget[USD]))
)

    it worked like:

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    adityah , a sum(Table[USD]) should work in table visual with all other columns

     

     

    If you need a data table,

     

    Summarize(Table, Table[Region], Table[Power BI], Table[Cat], "Actual", SUm(table[USD]))

     

    Learn Power BI: Calculatetable, Summarize, Groupby, SummarizeColumns, Except, Distinct, Generateseries, Generate, Crossjoin
    https://youtu.be/cN8AO3_vmlY?t=22350

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi adityah 

    try to add a calculated table like:

    Table = 
ADDCOLUMNS(
    SUMMARIZE(
        budget,
        budget[Region],
        budget[Power BI],
        budget[Cat.]
    ),
    "Actual",
    CALCULATE(SUM(budget[USD]))
)

    it worked like: