topic Total Across Row in DAX Commands and Tips

Total Across Row

Anonymous — Mon, 04 Jan 2021 10:37:36 GMT

Hi all,

I'm trying to get total across rows. I need a column, where each row has a value of summation across rows.

For example, in below screenshot, I need a column with total sales for each month. So basically it should add sales of each category for each month giving following results: So i need to calculate "Total Sales" column.

Month	Furniture	Office Supplies	Technology	Total Sales
JAN		3.52		3.52
FEB		597.13	14.99	612.12
MARCH		585.12	238	823.12
APRIL		303.03	99.99	403.02
JUNE	663.98	41.05		705.03
JULY		589.48	257.98	847.46
SEP		276.26	59.98	336.24

I was to create a DAX for this. Is there a way to so this? Also the values in each category is calculated using measure names "Sales LTM".

I'm sharing the link to my pbix file for your reference. Its on "page 2".

https://1drv.ms/u/s!AhWE64mO-7d3h3OqttDxPCRMzJsZ

Re: Total Across Row

amitchandak — Mon, 04 Jan 2021 10:44:51 GMT

Anonymous , No access to the file. What are your base table structure

As of now, it seems like

Sum(Table[Furniture])+Sum(Table[Office Supplies]) + Sum(Table[Technology])

or add a total column to the matrix (column total)

if you looking for a hybrid table

https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1354591
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428

vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc

Re: Total Across Row

Anonymous — Mon, 04 Jan 2021 10:59:30 GMT

Hi amitchandak

Thanks for the reply.

Firstly, I'm not able to use SUM() function because the values in each category is calculated using DAX. The maesure name is "Sales LTM". I believe that we cannot use a measure felid with SUM() function.

Secondly, i want a dynamic solution. Because if you notice in my pbix file as well as screenshot, I have added a slicer name "State". So for each state, category will change. So your solution will not work here.

Re: Total Across Row

amitchandak — Mon, 04 Jan 2021 12:12:23 GMT

Anonymous , i was not able to access the pbix, can share again.

Re: Total Across Row

Anonymous — Mon, 04 Jan 2021 12:25:16 GMT

amitchandak I'm sharing the link again. Open it in google crome. It will be open then.

https://1drv.ms/u/s!AhWE64mO-7d3h3OqttDxPCRMzJsZ

Re: Total Across Row

mahoneypat — Mon, 04 Jan 2021 13:02:17 GMT

I downloaded your pbix. When I just turn on Column subtotals in the matrix format options, I get your desired results. If those totals are not correct, this measure could be used instead.

Category Sum = SUMX(VALUES(Orders[Category]), [sales LTM])

Regards,

Pat

Re: Total Across Row

Anonymous — Mon, 04 Jan 2021 13:19:24 GMT

Hi mahoneypat,

Thanks for the reply. I don't want to use the subtotal option as I will use this total sales column in other calculations as well. Also I tried the code you have given and created "total sales". But when I add the measure in my matrix, it shows the result like below screenshot:

Basically it shows total sales for individual category. Not overall month sales. I want the result we get from subtotals, but for that I want to create DAX.

Re: Total Across Row

amitchandak — Mon, 04 Jan 2021 13:50:13 GMT

Anonymous , You can enable column sub total , will that do ?

Re: Total Across Row

Anonymous — Mon, 04 Jan 2021 14:17:31 GMT

amitchandak Actualy I want to create a DAX for this. Because I will further use this total sales in my other calculation. So subtotal option won't work in this case.

Re: Total Across Row

mahoneypat — Tue, 05 Jan 2021 02:48:56 GMT

You can get your total with this measure, but when you add it to matrix it will add it for each Category. Adding it as a single column is not doable.

Sales LTM All Cat = CALCULATE([sales LTM], ALL(Orders[Category]))

Regards,

Pat

Re: Total Across Row

Anonymous — Wed, 06 Jan 2021 11:19:21 GMT

Hey mahoneypat , Thanks for the solution. It works. Yeah I'm understanding thats it not possible in powerBI just to add measure in column in a matrix. But atleast I got solution of getting the correct total sales value.

Let me know if there is a way I can add measure field in column or rows instead of values. Again thats for the help.

Re: Total Across Row

mahoneypat — Wed, 06 Jan 2021 13:08:30 GMT

You may be interested in the Show On Rows feature.

You Can Now Put Values On Rows In Power BI – Data Savvy

Also, you can turn off auto size columns in the column header options, and reduce the width of any column you don't want to show until it is gone.

Regards,

Pat