How to summarize a table correctly

[adityah]

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:

Region	Power BI	USD	Type	Cat.
xx	Non-Motor	12500	Actual	CAPEX
xx	Non-Motor	510.51	Actual	OPEX
xx	Non-Motor	2473.671	Actual	OPEX
xx	Non-Motor	757.4359	Actual	OPEX
yy	Non-Motor	680.5167	Actual	OPEX
yy	Non-Motor	1118.632	Actual	CAPEX
yy	Motor	6992	Actual	CAPEX
yy	Motor	4736.987	Actual	CAPEX
yy	Motor	757.4359	Actual	OPEX
yy	Motor	1740	Actual	CAPEX
yy	Motor	2967.308	Actual	CAPEX
xx	Motor	2294.103	Actual	CAPEX
xx	Motor	3191.795	Actual	CAPEX

to look like this:

Region	Power BI	Actual	Cat.
xx	Non-Motor	12500	CAPEX
xx	Non-Motor	3741.616	OPEX
yy	Non-Motor	680.5167	OPEX
yy	Non-Motor	1118.632	CAPEX
yy	Motor	16436.29	CAPEX
yy	Motor	757.4359	OPEX
xx	Motor	2294.103	CAPEX

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

[amitchandak · Accepted solution]

@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 · Accepted solution]

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:

[amitchandak · Accepted solution]

@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