cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MojoGene Member
Member

Trying to turn off some coumn totals in a plain table (not matrix table)

I have a table with some columns for which I want totals and other columns for which I do not want totals. Some of the columns permit me to select "Don't summarize" in the Values pane, while other columns do not permit this. 

 

What is also odd is that there seems to be no control over how the totals are displayed. For example, one column in the table calculates the number of days between two events. What the Total row displays is the lowest number of any value in that column, which happens to be a meaningless piece of information for my purposes.

 

Is there any way to turn off particular column totals when the Visual pane does not provide the "Don't summarize" option?

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Trying to turn off some coumn totals in a plain table (not matrix table)

Hi @MojoGene,

I will note that the last two date fields are measures that display the maximum date. The "Days Unbilled" and "Days Since Opened/Last Payment" fields are are also measures based on the number of days between an event and the current date.

Could you try rewriting the four measures or creating new measures by using an IF statement like below(which should hide the total values for these four measures) to see if it works in your scenario? Smiley Happy

New Measure = IF ( HASONEVALUE ( 'Table1'[Client] ), [Days Unbilled] )

Note: You'll need to replace 'Table1' with your real table name.

 

Regards

5 REPLIES 5
v-ljerr-msft Super Contributor
Super Contributor

Re: Trying to turn off some coumn totals in a plain table (not matrix table)

Hi @MojoGene,


Is there any way to turn off particular column totals when the Visual pane does not provide the "Don't summarize" option?


I don't there is an option to turn off particular column totals in the Table visual currently.


What is also odd is that there seems to be no control over how the totals are displayed. For example, one column in the table calculates the number of days between two events. What the Total row displays is the lowest number of any value in that column, which happens to be a meaningless piece of information for my purposes.


Based on my experience, we should be able to adjust the formula to show the proper total on the Table visual in most scenarios.

 

Could you be more precisely with your issue by posting your table structures(including the relationships and the measures you're using) with some sample/mock data which can help us reproduce the issue, so that we can better assist on it? It's even better to just share a sample pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploadingSmiley Happy

 

Regards

MojoGene Member
Member

Re: Trying to turn off some coumn totals in a plain table (not matrix table)

Maybe this will help. Here is the table visual with the problematic total fields circled:

Screenshot 2017-12-22 08.53.27.jpg

 

PBI is apparently recognizing 3 different types of fields. When I right-click on any of the first 4 fields in the Values pane I get a pull-down menu as shown above. When I right-click on the next two fields, I get a pull-down menu that looks like this:

Screenshot 2017-12-22 08.53.47.jpg

 

When I right-click on any of the last 3 fields, I get a pull-down menu that looks like this:

Screenshot 2017-12-22 08.54.31.jpg

 

I will note that the last two date fields are measures that display the maximum date. The "Days Unbilled" and "Days Since Opened/Last Payment" fields are are also measures based on the number of days between an event and the current date.

 

There appears to be no way to control whether or how the totals are displayed in the circled areas.

MojoGene Member
Member

Re: Trying to turn off some coumn totals in a plain table (not matrix table)

Sorry for the quality of that first photo. Here is it again.

 

Table.jpg

v-ljerr-msft Super Contributor
Super Contributor

Re: Trying to turn off some coumn totals in a plain table (not matrix table)

Hi @MojoGene,

I will note that the last two date fields are measures that display the maximum date. The "Days Unbilled" and "Days Since Opened/Last Payment" fields are are also measures based on the number of days between an event and the current date.

Could you try rewriting the four measures or creating new measures by using an IF statement like below(which should hide the total values for these four measures) to see if it works in your scenario? Smiley Happy

New Measure = IF ( HASONEVALUE ( 'Table1'[Client] ), [Days Unbilled] )

Note: You'll need to replace 'Table1' with your real table name.

 

Regards

MojoGene Member
Member

Re: Trying to turn off some coumn totals in a plain table (not matrix table)

This suggestion worked perfectly.  Thanks very much for the help!