Table visual help

[vjnvinod]

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

[v-cherch-msft · Accepted solution]

Hi @vjnvinod

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

Regards,

Cherie

[v-cherch-msft]

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

Regards,

Cherie

[vjnvinod]

@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

[v-cherch-msft]

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

Regards,

Cherie

[vjnvinod]

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

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

[v-cherch-msft]

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.

Regards,

Cherie

[vjnvinod]

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

[v-cherch-msft]

Hi @vjnvinod

Here is the sample file.

Regards,

Cherie

[vjnvinod]

@v-cherch-msft

Hi, thanks for this, but when i looked at it, i noticed again that revenue numbers are correct, however the Total growth rate is not correct.

that means that my total should be 851M and growth should be at 2.3% but your measure output is total at 851M and growth rate is at 2.5%.

Can you have a look below and share the updated pbix file, for me to check, if it works?

Appreciate your expertise & help to fix this.

[v-cherch-msft · Accepted solution]

Hi @vjnvinod

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

Regards,

Cherie

[vjnvinod]

@v-cherch-msft

superhelpful, one  quick question as well

if i want to take/remove the last line, which is Total, is there a way to do that in power BI?

thanks for your help

[v-cherch-msft · Accepted solution]

Hi @vjnvinod

Just turn off the 'Row subtotal'.

Regards,

Cherie

[vjnvinod]

@v-cherch-msft

perfect, many thanks!

[Greg_Deckler]

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