Total Across Row

[sakhigarg]

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

[mahoneypat · Accepted solution]

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

[sakhigarg]

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.

[mahoneypat]

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

[amitchandak]

@sakhigarg , You can enable column sub total , will that do ?

[sakhigarg]

@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.

[mahoneypat]

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

[sakhigarg]

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.

[amitchandak]

@sakhigarg , 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/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

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

[sakhigarg]

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.

[amitchandak]

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

[sakhigarg]

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

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