Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
siva_powerbi
Helper IV
Helper IV

Static total column at end of matrix visual

I have a requirement to add the custom column to calculate the sum of selected columns in a Matrix visual. Tried different approaches adding caluclated columns, measures but nothing seem to add the new column at end of the matrix.

 

siva_powerbi_0-1606559997207.png

Need to remove total (Which I can do from Format option) and add the custom total column to add the May, June and July columns and display at end of the visual.

 

Struggling for last couple of days. Any help is appretiated.

 

Edit------------------------------------------------------------------------------------------------------------------------------

 

Input data to PoweBI

 

March100
April50
May70
June150
July170
August120
September90
October80

 

Now my requirement is to build the matrix visual and sum the selected months, by default matrix visual will sum all columns and show as total at the end, but I need to sum only May, June and July columns and show at end of the Visual in place of total.

 

Total should be 390 instead of 830

 

didn't find any option to add the pbix file, hope my requirement is clear now.

 

Thanks

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

Test this measure expression to get the table that is displayed. Replace the table and column names with the actual ones in the model. You can also replace SUM( ) with your current measure.

mahoneypat_0-1606570449543.png

Custom Total ?
YES (
COUNTROWS (
DISTINCT ( CustomTotal[Month] )
) 1,
SUM ( CustomTotal[Amount] ),
CALCULAR (
SUM ( CustomTotal[Amount] ),
CustomTotal[Month]
EN THE UNIT OF
"May",
"June",
"Nice stay"
}
)
)

Best regards

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

6 REPLIES 6
mahoneypat
Microsoft Employee
Microsoft Employee

I'm guessing you made a calculated column with that expression.  Is that right?  It is supposed to be a measure.

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Yes, correct I used it as the Column instead of Measure.

mahoneypat
Microsoft Employee
Microsoft Employee

Test this measure expression to get the table that is displayed. Replace the table and column names with the actual ones in the model. You can also replace SUM( ) with your current measure.

mahoneypat_0-1606570449543.png

Custom Total ?
YES (
COUNTROWS (
DISTINCT ( CustomTotal[Month] )
) 1,
SUM ( CustomTotal[Amount] ),
CALCULAR (
SUM ( CustomTotal[Amount] ),
CustomTotal[Month]
EN THE UNIT OF
"May",
"June",
"Nice stay"
}
)
)

Best regards

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat  Thanks for the Answer.

 

I used the formula but it's acting as filter and removed the months from the visual that are not in the calculation.

 

Am I missing anything?

 

siva_powerbi_0-1606574043791.png

 

Fowmy
Super User
Super User

@siva_powerbi 

What are your exact requirements?


You can add calculated columns to the underlaying table and being on to the matrix visual. 

 

Provide sample data and the expected results to understand the probelm.

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn
 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy  Edited question, please check

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors