Power BI Matrix Visual to show 0 values in row subtotal but not in drill through

[tim_001]

Hi, 

I have a Matrix Table with Sales Information by Sales Rep, State and year. To keep the same layout so that every year is visible I created a SUM measure of the Sales to show 0 values. 

This is how the matrix looks with SUM measure - which is good on the highest level and how I want it: 

	2020	2021	2022
Tim	10000	500	0
Tom	0	0	500
Theodore	0	4000	8000

Now, if I drill through to see in which states how much was sold - it looks like this: 

	2020	2021	2022
Tim	10000	500	0
Alabama	500	0	0
Alaska	0	0	0
Arizona	0	0	0
Arkansas	0	0	0
California	0	0	0
Colorado	0	0	0
Connecticut	0	0	0
Delaware	0	0	0
Florida	9500	0	0
Georgia	0	500	0
Tom	0	0	500
Theodore	0	4000	8000

But I would like to have it look like this - without the 0 values in the drill through:

	2020	2021	2022
Tim	10000	500	0
Alabama	500	0	0
Florida	9500	0	0
Georgia	0	500	0
Tom	0	0	500
Theodore	0	4000	8000

It might be something simple that I am just not seeing right now - But I appreciated any support, idea or comment. 

Thanks a lot

Tim

[Jai-Rathinavel · Accepted solution]

@tim_001 To fix this you can create another measure to check if the row has a sale value for all the years or not. 

1. Create a measure like below

Has Sales ? = IF([Sum of Sales] <> 0, 1, BLANK())

2. Now add this measure to your matrix's visual filter pane.

3. Set the Has Sales ?  measure filter to 1

4. Now you should not rows with the all the years as 0 sales

Thanks,

Jai

[v-pgoloju]

Hi @tim_001,

Just following up to see if the Response provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @tim_001,

Just following up to see if the Response provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @tim_001,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @Yashwanthsathya , @Jai-Rathinavel, @Jai-Rathinavel and @Selva-Salimi  for prompt and helpful responses.

Just following up to see if the Response provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[Selva-Salimi · Accepted solution]

Hi @tim_001 ,

you can write a measure as follows:

measure X := var tbl=summarize (your_table, sales_rep, state, year, "sl_amount", sum(your_table[sales]))

return

if(sumx(tbl, sl_amount)=0,0,1)

now you can use this measure in the filter pane of your matrix visualization and set it to 1. in this cse, you can have your prefered output.

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[Jai-Rathinavel · Accepted solution]

@tim_001 To fix this you can create another measure to check if the row has a sale value for all the years or not. 

1. Create a measure like below

Has Sales ? = IF([Sum of Sales] <> 0, 1, BLANK())

2. Now add this measure to your matrix's visual filter pane.

3. Set the Has Sales ?  measure filter to 1

4. Now you should not rows with the all the years as 0 sales

Thanks,

Jai

[tim_001]

Hi @Jai-Rathinavel - I applied suggestion and it works well to show 0 values in the rows but in the drill down only the states with sales.

The only issue occuring now is that if Tim, Tom or Theodore have no Sales, they are not shown at all in table. I would like to see them in the row with all 0 values, so that they are still existing but did not make any Sales in those years. 

Can I tweak the formula for it?

[Jai-Rathinavel]

@tim_001  It would be greate if you can help me with a sample data. If you are looking for Yearly Sales you can refer the below formula

Yearly Sales = CALCULATE([Sum Sales], ALLEXCEPT(Sales, Sales[Year])

[tim_001]

@Jai-Rathinavel 

Data looks like this for instance - but the issue is if I use your workaround - Tom would disappear in below table because he did not sell anything. 

2020	2021	2022
Tim	10000	500	0
Alabama	500	0	0
Florida	9500	0	0
Georgia	0	500	0
Tom	0	0	0
Theodore	0	4000	8000

[Jai-Rathinavel]

@tim_001 Replace your existing measure filter expression with this and see if this helps

Has Sales ? = IF(ISINSCOPE(States[State]), IF ( [Sum Sales] <> 0, 1, BLANK() ),1)

Thanks,

Jai

[tim_001]

@Jai-Rathinavel Thanks for the suggestion and continous support. Unfortunately, Tom remains with 0 values invisible.

I manage to get the visual if I use blanks instead of "0" but for the visual I prefer to show zeros instead of blanks. I think that is complicating it and not sure if it is possible. I tried with a dim table and it works so far, but again only if I just use the Sum and not Sum +0 to show in the table 0 values instead of blanks

[Royel]

Hi @tim_001 i can see @MasonMA  give you an easy fix you can try this. Just a quich correction in the dax 

Corrected DAX 

Sales = 
IF(
    SUM(Sales[Amount]) = 0,
    BLANK(),
    SUM(Sales[Amount])
)

use it and let us know your progress. 

Thanks 

[Selva-Salimi]

Hi @tim_001 ,

it seems that you use a dimension table values in drill through (they are not from the same table) or different columns in measure and visual. if not, please share your measure.

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[Yashwanthsathya]

Create a measure that would look like this :

VAR Sales_Original = SALES
VAR Sales_Custom = IF(SALES = 0, BLANK(), SALES)
IF(
ISINSCOPE(Sales_Rep), Sales_Custom, Sales_Original
)

This should display the Sales with 0 as  BLANK for the Sales_Rep in drill down. Let me know if this worked.
----
Yash

[MasonMA]

Hi, the regular approach would be to create a Measure like 

_Sales = 

IF ( SUM ( Sales[Amount]) = 0,

     BLANK(),

     SUM ( Sales[Amount]))

 

In your matrix, replace the current measure with _Sales.

Then go to Matrix visual settings, switch on 'Show items with no data' only for Year but not for State. This way every year shows for each rep, but states with no sales will stay hidden.