topic How to summarize a table correctly in DAX Commands and Tips

How to summarize a table correctly

adityah — Wed, 10 May 2023 03:59:07 GMT

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'

Re: How to summarize a table correctly

amitchandak — Wed, 10 May 2023 06:01:21 GMT

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

Re: How to summarize a table correctly

FreemanZ — Wed, 10 May 2023 06:23:16 GMT

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: