cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

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
Super User IV
Super User IV

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
Super User IV
Super User IV

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.

Super User IV
Super User IV

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

@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

 

Super User IV
Super User IV

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

Proud to be a Super User!

Website   YouTube    LinkedIn

@Fowmy  Edited question, please check

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors