How to sum certain columns in power bi matrix visual

[riyadhiut]

Hello Experts,
I have 20+ columns and 15+rows  in my power bi matrix visual.Please check  my visual below.My goal is to calculate sum from Month0 to Month 22 and display under "Total" column.If i enable "Column Subtotal" option only value of first column is shown under "Total" column.Kindly assist me on this.

Regards,
Riyadh 

[v-lionel-msft]

Hi @riyadhiut ,

This is a common problem. Many users in the forum have asked similar questions, which are generally caused by the aggregation of formulas. You can search and refer to them.

Rows and Column Totals are wrong in a Power BI Matrix 

Wrong column totals (table)  

...

In addition, you can also provide your DAX formula for us to analyze the error.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[newpowerbiuser8]

Hi, 

I am confused. I have excel table which when converted to powerbi mtarix is showing unique values for each ctagory, which is correct, the the total is showing is incorrect. It is only counting unique values however if you see, Jeans order apperas twice so it should calculate total based on unique category. I know power BI doesnt let that happen automcatlly, can you please tell a work around?

Thanks a lot

DAx used : 

# Total Regs = CALCULATE(DISTINCTCOUNT('Table'[COLUMN]),FILTER('tABLE','tABLE'[COLUMN]<>"No data"))

[Greg_Deckler]

@riyadhiut - It's difficult to say for sure with the information provided. It could be all kinds of things. Not sure of what the source data looks like, etc. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

But, a couple of guesses. Perhaps it is a measure totals issue? Quick Measure, Measure Totals, The Final Word should get you what you need: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Maybe multi-column aggregations? https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-...

Maybe you need to unpivot your month columns in your source?

[newpowerbiuser8]

Hi, 

I am confused. I have excel table which when converted to powerbi mtarix is showing unique values for each ctagory, which is correct, the the total is showing is incorrect. It is only counting unique values however if you see, Jeans order apperas twice so it should calculate total based on unique category. I know power BI doesnt let that happen automcatlly, can you please tell a work around?

Thanks a lot

DAx used : 

# Total Regs = CALCULATE(DISTINCTCOUNT('Table'[COLUMN]),FILTER('TABLE','TABLE'[COLUMN]<>"No data"))

[parry2k]

@riyadhiut one way could be to unpivot your data:

- transform data
- select Month Year column

- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply

To visualize,
- matrix visual:
- add product and region on rows,
- add attribute on columns
- add value on values section

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!