cancel
Showing results 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

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.

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

 March 100 April 50 May 70 June 150 July 170 August 120 September 90 October 80

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
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.

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!

@mahoneypa HoosierBI on YouTube

6 REPLIES 6
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!

@mahoneypa HoosierBI on YouTube

Helper IV

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

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.

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!

@mahoneypa HoosierBI on YouTube

Helper IV

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

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 🙂

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

@Fowmy  Edited question, please check

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.