Ok. If you want zeros to appear on the canvas visual you type your measure and add a 0. Something like SUM(RevenueCheck[Check Amount])+0 . Thats how i use it to display zeros in my reports. Furthermore, I apply conditional formatting like in excel . I usually use this simple custom formatting in powerbi #,##0.00;(#,##0.00);- . I usually like to display - so its easy to browse on the report
haha! Tell me about it . I learned this trick years ago in a DAX training class! People in the room were making these fancy ifs statements with isblank [total revenue],0[total revenue] but the calculation was very slow . And if you have sparce Matrix, well good luck because it will take a while a while to show the results! . Thanks
@@bcti-bcti Oh yeah! I have had a few of those in the office specifically when the data has mismatches. Thats my favorite! I always tell my colleagues before you go deep in a formula: Do a sanity check of your data before you start doing Xlookup Index Match etc. But sometimes people don't listen!
The VBA code should hide or display all row/column fields at the same time. If you want to turn on or off only 1 of many row entries, you can use the code below: ActiveSheet.PivotTables(1).PivotFields("Region").EnableItemSelection = Not ActiveSheet.PivotTables(1).PivotFields("Region").EnableItemSelection Change the word "Region" to the name of the field you are trying to hide or show
I’m sorry. I just realized that your question was about a different video than I thought. I thought you were asking about the “Hide Filter Controls in a Pivot Table” video. Did you mean to write “have TWO fields”?
Now that I properly understand your question: If you were to add another field to the ROWS (ex: State then Supplier), you would need to create another table of unique suppliers, the same way you created a unique table of states. Then link the FACT table to the new table. You would use the new table's field instead of the companion fiend in the FACT table. Hope this helps.
Excellent tutorial 👍and thanks for sharing the tip 🌹. I am just wondering why creating a measure from the main table somethign like "SalesMeasure = sum(BCTI_Sales[Sales])+0" does not work while adding the unique state table (like related dimension table) works in your solution? Please kindly explain. Thank you in advance.
This would require another video to explain properly. I don't think I can do the explanation justice in a comment reply. I will try to put something together in the (very) near future to explain why this works when using the distinct list of states versus the states in the Fact Table. Thanks for watching.
@@bcti-bcti If I change the measure to Sales with Zeroes = SUM(BCTI_Sales[Sales]) * 1, it does not work. I think in most situations multifying by 1 and adding 0 should have had the same force calculation effect, but apparently not in this case. I also try to use measure like this: SalesMeasure = var SalesMeasure = sum(BCTI_Sales[Sales]) return if (isblank (SalesMeasure), 0, SalesMeasure). It does not work. When you do the follow-up video, please also kindly explain why is this. Thank you for your creative ideas and look forward to your follow-up video to clear my query on this. Thanks again for all your awesome videos. 🌹🌹❤❤
Yeah, that's the issue with showing zero (like when using an IFERROR in Excel); it could mislead the viewer into thinking that the average is 0. Additional logic would be needed to counter that.
Another great tip for visualisation in Excel & Power BI
Thank you for sharing and the clean and lucid explanation.
Ok. If you want zeros to appear on the canvas visual you type your measure and add a 0. Something like SUM(RevenueCheck[Check Amount])+0 . Thats how i use it to display zeros in my reports. Furthermore, I apply conditional formatting like in excel . I usually use this simple custom formatting in powerbi #,##0.00;(#,##0.00);- . I usually like to display - so its easy to browse on the report
I agree with you. I learned that trick AFTER I had made this video. That's a much easier way. Thanks for watching and contributing.
haha! Tell me about it . I learned this trick years ago in a DAX training class! People in the room were making these fancy ifs statements with isblank [total revenue],0[total revenue] but the calculation was very slow . And if you have sparce Matrix, well good luck because it will take a while a while to show the results! . Thanks
@@jazzista1967 isn't it a great feeling when you can solve a complex problem simply? Especiall in front of a group of people solving it laboriously.
@@bcti-bcti Oh yeah! I have had a few of those in the office specifically when the data has mismatches. Thats my favorite! I always tell my colleagues before you go deep in a formula: Do a sanity check of your data before you start doing Xlookup Index Match etc. But sometimes people don't listen!
What if I have to fields as Rows in the Matrix? Thank you for your time to help us by the way.
The VBA code should hide or display all row/column fields at the same time. If you want to turn on or off only 1 of many row entries, you can use the code below:
ActiveSheet.PivotTables(1).PivotFields("Region").EnableItemSelection = Not ActiveSheet.PivotTables(1).PivotFields("Region").EnableItemSelection
Change the word "Region" to the name of the field you are trying to hide or show
I’m sorry. I just realized that your question was about a different video than I thought. I thought you were asking about the “Hide Filter Controls in a Pivot Table” video. Did you mean to write “have TWO fields”?
Now that I properly understand your question: If you were to add another field to the ROWS (ex: State then Supplier), you would need to create another table of unique suppliers, the same way you created a unique table of states. Then link the FACT table to the new table. You would use the new table's field instead of the companion fiend in the FACT table. Hope this helps.
Excellent tutorial 👍and thanks for sharing the tip 🌹. I am just wondering why creating a measure from the main table somethign like "SalesMeasure = sum(BCTI_Sales[Sales])+0" does not work while adding the unique state table (like related dimension table) works in your solution? Please kindly explain. Thank you in advance.
This would require another video to explain properly. I don't think I can do the explanation justice in a comment reply. I will try to put something together in the (very) near future to explain why this works when using the distinct list of states versus the states in the Fact Table. Thanks for watching.
@@bcti-bcti Thank you for your reply and looking forward to your further video about this. Thank you so much. I really love the stuff in your channel😍
@@bcti-bcti If I change the measure to Sales with Zeroes = SUM(BCTI_Sales[Sales]) * 1, it does not work. I think in most situations multifying by 1 and adding 0 should have had the same force calculation effect, but apparently not in this case. I also try to use measure like this: SalesMeasure = var SalesMeasure = sum(BCTI_Sales[Sales]) return if (isblank (SalesMeasure), 0, SalesMeasure). It does not work. When you do the follow-up video, please also kindly explain why is this. Thank you for your creative ideas and look forward to your follow-up video to clear my query on this. Thanks again for all your awesome videos. 🌹🌹❤❤
The "Formatting Options" button does not appear in my Excel.
How to make it appear? 🤔
Is this when you are applying Conditional Formatting to a Pivot Table or just a regular table?
@@bcti-bcti I was wrong: the button appears in pivot tables.
Thanks for the tip. 🤗
Glad it finally decided to show it's pretty face.@@JoseAntonioMorato
Nice solution….but beware of average calculations
Yeah, that's the issue with showing zero (like when using an IFERROR in Excel); it could mislead the viewer into thinking that the average is 0. Additional logic would be needed to counter that.