How to get the total value at the end in matrix table?

[bikelley]

Hello,

Can you please help me to get the Total at the end column only (Sum of Values)?

Sample file 

https://drive.google.com/file/d/1YWpad3SKLfT3wnbkDW1WnWjsGRayJhXl/view 

I have a matrix table but values are on average. I want to add a sum of values at the end, but when I try to add the Total it is showing by column level as well. Can anybody please let me know how to do it only at the end? I also tried online and did not find anything useful, coz I already have value as average on my matrix table and I just need a sum at the end.  Please see below what I did.

Current table showing the average. 

When I try to add Total at the end, it shows by column level also. which I don't need. 

What I tried, 

Tota_New = IF(ISFILTERED(d_Summary[RegionsSTK]), SUM('d_line (2)'[Value]))
Total = SUM('d_line (2)'[Value])

 Expect results without highlighted columns, just the sum of the total at the end. 

 Thank you so much

[v-yalanwu-msft · Accepted solution]

Hi, @bikelley ;

Please try :

1.create a column.

sort = RANKX('Timeline',[Status],,ASC,Dense)

2.create a new table.

Newtable = 
var _a=SUMMARIZE('Timeline',[ID],[Status],[sort],"value",AVERAGE(Timeline[Value]))
var _b=SUMMARIZE('Timeline',"ID",BLANK(),"Status","Average","sort",DISTINCTCOUNT(Timeline[Status])+1,"value",BLANK())
return UNION(_a,_b)

3.create a measure.

Measure = IF(SELECTEDVALUE('Newtable'[Status])="Average",AVERAGE('Timeline'[Value]),CALCULATE(SUM('Newtable'[value]), TREATAS(VALUES('Timeline'[ID]),'Newtable'[ID])))

4.sort by "sort" column.

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bikelley]

@v-yalanwu-msft you are a rock start my friend, Thank you so much. I almost gave up on this and I saw your post. Thank you so much. This work as I wanted.  Again, I really appreciate your help. 

[parry2k]

@bikelley hang on a second, is that total a measure you have on the value section of the matrix visual?

[parry2k]

@bikelley not sure what you are doing? it should be pretty straightforward, send me the pbix file. 

[bikelley]

HI @parry2k 

Please see the link below for the sample file. 

https://drive.google.com/file/d/1YWpad3SKLfT3wnbkDW1WnWjsGRayJhXl/view?usp=sharing 

Again, I just want to make sure we are on the same page and I am not wasting your time. I want everything except the columns highlighted in yellow (they are the totals). Sorry that I keep repeating this. 

Thank you so much. I really appreciate your time and help. 

[parry2k]

@bikelley on the format pane, under subtotal, you can turn on "Per Column Level" and turn off the total at the column level.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[bikelley]

HI @parry2k 

Thank you so much for your time, but I tried what you said, and still the same, I want to get rid of the Total for 2,3,4,5 columns, and just keep the end Total column. Am I doing something wrong?