Shows a 0 in the chart instead of removing dates

[PowerBI_User20]

Hi guys,

I have the following chart:

If there is no sales for vendor 3 in 2019 (as you can see in the following example) Power BI removes the value from the chart, how can I keep the information in the chart with a 0 value?

Is there any option in the chart instead of creating new measures?

Thanks in advance.

Regards.

[PC2790 · Accepted solution]

If you are using a measure try adding 0 to your measure,example:

TotalSum = sum(ColumnName)+0

This might have performance implications if your data is huge

You can consider using the Count aggregation in the visualisation window and then click on the dropdown of the field in that particular visual and select "Show items with no data"

[PowerBI_User20]

Hi @PC2790,

Thank you for your help. 

This works for a chart but not for a table, do you know how to do the same in a table?

Thanks in advance.

[DemoFour]

Hi @PowerBI_User20 

For a table you can wrap your measure in an IF statement to show a 0 if the value has no data returned in the measure. 
Or if you are not using Explicit measures (just dragging and dropping a column into a visual - Implicit measure - and choosing how it is show e.g. Sum, Count, Min etc. ) then consider writing a measure for use in the visualisation. 

[PowerBI_User20]

Hi @DemoFour,

Thank you for your answer, is there a way easier than using switch measure?

I have tried the following one but it doesn't work:

Calculate = if(sum(T_Sales[Sale])=0;0;sum(T_Sales[Sale]))

The table I have is a Percent of Total Column of the measure Calculate

Thanks in advance.

[DemoFour]

Hay @PowerBI_User20  

Without seeing your model etc. I can guide you to how I have done this, but I may need specifics to suit your current use. 

I have used the following code to show a 0 if the measure is blank

IF(
ISBLANK( [Measure] ),
0,
[Measure]
)

By wrapping your measure in this statement, you will return a 0 when the measure has no value, but return the measure if not. 

You can also set this up with Variables if you want to do other calculations to return:

HLD At Risk Total = 
VAR Total = 
CALCULATE(
    [Total HLD], -- This is a measaure to sum the column
    'HLD'[HLD Progress] = "At Risk"
)
VAR Result = 
IF( 
    ISBLANK( Total ),
    0,
    Total
)
RETURN
    Result

I hope this helps to guide you to a solution that works. 

[PowerBI_User20]

Hi @DemoFour,

Thank you for your answer.

Your solution works if I select the measure with no calculation, as you can see:

But if I show the measure as Percentage of Column Total it doesn't work (and I need to show it on this way)

[DemoFour]

@PowerBI_User20 

Are you writing a measure to work out the % Total ?

or

Choosing to show the measure as a % of total column, in the UI of the matrix visual?

This is where writing explicit measures, is the key to showing the values you want. That way the IF ISBLANK function can still work to show the measures with 0%. 

To understand more of this within your model, it will help to share a pbix file or some dummy data to test out solutions within. 

I hope this helps you on your way to a better solution. 

[PowerBI_User20]

Hi @DemoFour,

I have created a sample data.

The link to download it is: https://www.dropbox.com/s/983l8nd2mci9z3x/Prueba.pbix?dl=0

As you can see with this, if you go to the page 'Operaciones países' and select a country (it is called País in spanish) such as Costa Rica, you will see how the chart keep the format but not the tables below.

[DemoFour]

Hi @PowerBI_User20  Sorry for the delay, busy day! 

I have had a look at your test report, and I am not sure what you are trying to achieve.  

Do you want the table to show 0 for years with no sales e.g. Portugal which shows values in the matrix for 2021 & 22 but not for 2019 & 20? 

Can you please elaborate further on the expected output? 

Have you also considered having a hover over tool tip for the matrix; when you move the mouse over a Country, to show what you want? Rather than both graph and matrix, on the same pane.

[PowerBI_User20]

Hi @DemoFour,

Don't worry. I want that the columns and rows without data shows 0% value, but actually it dissapears.

Now if I select Portugal for example I obtain this in the table:

I would like to obtain this one:

[DemoFour]

@PowerBI_User20 

If you don't want the graph or the slicer to filter the matrix, you can edit the interactions on the Format Tab. Change how visuals interact in a report - Power BI | Microsoft Docs

This way you can set the page up to interact how you want it to, rather than all visuals being dynamic from clicking the visuals. 

[PowerBI_User20]

I need that the slicer filter the matrix, because if I select Portugal the value of the rest of countries/regions should be 0.

I just want that the another values shows value 0 or nothing but that they don't dissapear

[DemoFour]

Morning @PowerBI_User20 

I have had a look at the report and I am still not sure what your output is, as the filter context would mean that the slicer selection would show the values in the slicer. 

I have duplicated the page in your report and I think this is what you require. I cant share a link back as my org blocks this, so if you are happy to PM a email I can send this to you? 

[PowerBI_User20]

Hi @DemoFour,

Of course, let me check it and and I will tell you if that is what I am looking for.

You can send that to me to tresmandiles1@hotmail.es

Thank you in advance.

Regards.

[DemoFour]

@PowerBI_User20 

No worries, sent via email. 

[PowerBI_User20]

I have choosen to show the measure as a % of total column in the matrix visual.

I am trying to prepare a pbix file with dummy data to test it but it doesn't work as I expected. I will let you know when I share it.