Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @vjnvinod
Create Growth.2 measure for your reference. Here is the sample file.
Regards,
Cherie
Hi @vjnvinod
Just turn off the 'Row subtotal'.
Regards,
Cherie
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
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
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
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] ) ) )
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?
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
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.
Hi @vjnvinod
Here is the sample file.
Regards,
Cherie
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.
Hi @vjnvinod
Create Growth.2 measure for your reference. Here is the sample file.
Regards,
Cherie
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
Hi @vjnvinod
Just turn off the 'Row subtotal'.
Regards,
Cherie
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |