Forum Discussion
Total Across Row
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
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
11 Replies
- amitchandak
Super User
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#M428vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebcHi 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
Anonymous , i was not able to access the pbix, can share again.
- mahoneypat
Microsoft Employee
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,PatHi 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
Anonymous , You can enable column sub total , will that do ?
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
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,PatHey 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
