Power BI - Zero Filled Matrix (plus: Conditional Formatting trick)

Рет қаралды 2,053

BCTI

Күн бұрын

Пікірлер: 20

@robbe58 8 ай бұрын

Another great tip for visualisation in Excel & Power BI Thank you for sharing and the clean and lucid explanation.

@jazzista1967 6 ай бұрын

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

@bcti-bcti 6 ай бұрын

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.

@jazzista1967 6 ай бұрын

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 6 ай бұрын

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

@jazzista1967 6 ай бұрын

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

@RonDavidowicz 8 ай бұрын

Nice solution….but beware of average calculations

@bcti-bcti 8 ай бұрын

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.

@kebincui 8 ай бұрын

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.

@bcti-bcti 8 ай бұрын

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.

@kebincui 8 ай бұрын

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

@kebincui 8 ай бұрын

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

@kevinruiz9624 7 ай бұрын

What if I have to fields as Rows in the Matrix? Thank you for your time to help us by the way.

@bcti-bcti 7 ай бұрын

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

@bcti-bcti 7 ай бұрын

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

@bcti-bcti 7 ай бұрын

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.