cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vjnvinod Member
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

 

visual filter issue.JPG

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Microsoft v-cherch-msft
Microsoft

Re: Table visual help

Hi @vjnvinod

 

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.

View solution in original post

Microsoft v-cherch-msft
Microsoft

Re: Table visual help

Hi @vjnvinod

 

Just turn off the 'Row subtotal'.

1.png

 

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.

View solution in original post

14 REPLIES 14
Super User
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Microsoft v-cherch-msft
Microsoft

Re: Table visual help

Hi @vjnvinod

 

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] ) )
)

1.png

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

Apprecaite your help

 

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

 

Regards, vinod

Microsoft v-cherch-msft
Microsoft

Re: Table visual help

Hi @vjnvinod

 

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] ) )
)

1.png

 

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.
vjnvinod Member
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] ) )
)

 

vjnvinod Member
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

 

visuals issue.JPG

 

grwthissue.JPG

 

Can you recheck the measure?

Microsoft v-cherch-msft
Microsoft

Re: Table visual help

Hi @vjnvinod

 

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.

1.png

 

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

 

thanks for your help.

 

 

Microsoft v-cherch-msft
Microsoft

Re: Table visual help

Hi @vjnvinod

 

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.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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