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

matrix subtotal formatting

Does anyone know a way to have the column and row values in a matrix be formatted without decimals and have the subtotal values with a decimal?

 

My original values are all whole numbers but the measure produces an average which I'd like to show decimals for.

1 ACCEPTED SOLUTION

@xarius

 

That's not a problem. What field are you using in rows of your matrix visual? That is the one you have to use in ISFILTERED( ). That's what I meant by

ISFILTERED ( Table1[ColumnInRowsOfTheMatrix] )

Using it like that, the ISFILTERED tells you where you are in the matrix visual, Total or not Total.

If you share the pbix or at least show the structure of your table(s), the code of your measure and how you build the matrix visual where you use the measure (i.e. what fields go where) I will be able to help you further.

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

Hi @xarius

 

One way to do it would be to use ISFILTERED to determine whther you are in the Total row and FORMAT. Create a measure like:

 

 

MeasureDiffFormat =
VAR _Result = Write here the code for your calculation
RETURN
    IF (
        ISFILTERED ( Table1[ColumnInRowsOfTheMarix] ),
        FORMAT ( _Result, "#,#" ), //When not in the Total row
        FORMAT ( _Result, "#,#.##" )//In the Total row
    )

 

Thanks for replying @AlB.

 

I'm still not sure how I work out if I am in the total row/column of the matrix though. I have a measure as values for the matrix as opposed to the values being directly from a column, so I'm not sure using ISFILTERED will work in my case.

 

@xarius

 

That's not a problem. What field are you using in rows of your matrix visual? That is the one you have to use in ISFILTERED( ). That's what I meant by

ISFILTERED ( Table1[ColumnInRowsOfTheMatrix] )

Using it like that, the ISFILTERED tells you where you are in the matrix visual, Total or not Total.

If you share the pbix or at least show the structure of your table(s), the code of your measure and how you build the matrix visual where you use the measure (i.e. what fields go where) I will be able to help you further.

Ah! So what I wanted was the column subtotals rather than the row subtotals, and as I had the row subtotals switched off I didn't see that it was actually working. I managed to amend it so the column to filter on was correct to format the total column, thank you!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!