cancel
Showing results for
Did you mean:
Member

## Table visual help

Dear community,

Take a look at the below pic. its a table visual in power BI

my problem is, i need to hide or filter out some fileds lets say the one you see here "im" which has -57.1M. while we do that we also need to make sure the total is unaffected.(that means even i take off/hide  "-57.1M", my total should remain as 851.6M

any help on this will be highly appreciated

2 ACCEPTED SOLUTIONS

Accepted Solutions
Microsoft

## Re: Table visual help

Create Growth.2 measure for your reference. Here is the sample file.

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft

## Re: Table visual help

Just turn off the 'Row subtotal'.

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
14 REPLIES 14
Super User

## Re: Table visual help

You will need to write your measure according to ignore your filtering at the Total level. Take a look at this Quick Measure, Measure Totals, The Final Word as you should be able to use the technique here to get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Microsoft

## Re: Table visual help

You may use visual level filter to hide some fileds.Then you may create a measure with HASONEVALUE Function. Show a simplified sample as below. If it is not your case, please share some sample data for your scenario so that we could help further on it.

```Test =
IF (
HASONEVALUE ( Table3[Column1] ),
CALCULATE ( SUM ( Table3[Column2] ) ),
CALCULATE ( SUM ( Table3[Column2] ), ALL ( Table3[Column1] ) )
)```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

## Re: Table visual help

@v-cherch-msft

Hi take a look at the below sample.

i wasn't sucesfull to get there, may be because i am not good at caculating measure.

in this test file, i basically don't want to see cbs & elim & unclassified, but the total should remain same.

https://www.dropbox.com/s/jvpzkfyq82w6540/Test.pbix?dl=0

Regards, vinod

Microsoft

## Re: Table visual help

You may try to use below measure and set the visual level filter as below:

```Test =
IF (
HASONEVALUE ( Inbound[Line] ),
CALCULATE ( SUM ( Inbound[TER_FYTD_USD] ) ),
CALCULATE ( SUM ( Inbound[TER_FYTD_USD] ), ALL ( Inbound[Line] ) )
)```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

## Re: Table visual help

@v-cherch-msft

this works, i have 2 question on this

1) this would mean that i will have to create multiple measure, if i have more tables in my visuals?

2) my bad, can you help me understand the logic behind this measure?

why is caculate function is used twice for the same field?

```IF (
HASONEVALUE ( Inbound[Line] ),
CALCULATE ( SUM ( Inbound[TER_FYTD_USD] ) ),
CALCULATE ( SUM ( Inbound[TER_FYTD_USD] ), ALL ( Inbound[Line] ) )
)```

Member

## Re: Table visual help

@v-cherch-msft

I did a test, i think this measure doesn't works perfectly, i see the revenue numbers are ok with this measure but the growth% are affecting while filtering the fields in the visual filters, refer below pics

Can you recheck the measure?

Microsoft

## Re: Table visual help

You may use HASONEVALUE Function to change the total value and then create a new growth% measure with the same way. ALL function could get the total value by ignoring any filters.

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

## Re: Table visual help

@v-cherch-msft

thanks for this, i think i am not able to follow.

Can you share the pbix file? for me to take a look, to know how you arrived at ignoring filters for the growth.

Microsoft

## Re: Table visual help

Here is the sample file.

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,650)