How to get a total of rows in a table without SUM

[lennox25]

Hi,

I have a table much like the one below in my Power BI Visual. I need to total but not using SUM as this provides incorrect figures. The amount sold is not SUM, I have selected 'Dont Summarize'. How to I get a total row like the one I have manuall added to this table?

SIRAccountingPeriod	Week No	Store NO	Product	Amount Sold
202301	1	1007	Apples	4215
202301	1	1007	Apples	13854
202301	1	1007	Oranges	19631
202301	1	1007	Pears	20478
202301	1	1007	Oranges	29447
			TOTAL	87625

[mlsx4 · Accepted solution]

Hi @lennox25 

I don't know why not to use SUM. It works perfectly:

Sum Amount Sold = SUM(MyTable[Amount Sold])

If you want to keep the values not summarized, you can do this (but it is to reverse everything):

Medida = SUMX(VALUES(MyTable[Product]),CALCULATE([Sum Amount Sold]))

[lennox25]

I need to do it on the store number product is irrelevant. When I try this happens.

[mlsx4]

Then, replace the values field:

Medida = SUMX(VALUES(MyTable[Store NO]),CALCULATE([Sum Amount Sold]))

[lennox25]

I have produced a sample file to show you. PLease take a look and help if you can 🙂

https://drive.google.com/file/d/1gXR-zoc9qxcPSVDbNyJqqE2Ufe-ZQ4iV/view?usp=sharing

[mlsx4]

Hi, you have a date filter in the filter pane. Is it necessary?

[lennox25]

No its not necessary