How to include data that is not in the dataset?

[smlewisv]

Hello all,

I have to present a table/matrix in a dashboard that includes the amount and a percentage of that amount from the total of deliveries separated by Delivery Type.

Our company only does 1 kind of delivery but the dashboard has to show 3 types (the other 2 types that we don't do have to show '0') but the 3 types have to be shown.

I was able to calculate this and present it as a Matrix like this:

I put a Matrix and as the values I included the count of orders and the total of orders and for the Rows I put the Service Type.

And I got that result. BUT, since the data doesn't come with the other type of deliveries I cannot include these rows as requested.

I modified the SQL statement and created a different table with the types and the total orders for each my marking each row with the apropriate type (created 4 new fields) and it looks like this:

The main question here is how can I include the percentages in this new visual? I tried with Service Type but since the dataset doesn't contain any RoomOfChoice or Threshold then it's not included (like in the first example).

BTW, this last visual has the values switched into rows and basically displays the sum of all the "flags" I included.

Thank you all!

[smlewisv]

But that would work if I have 2 tables and a relation, right? One table with the categories and the other whith the deliveries.

The problem is that I was given this dashboard with one large dataset that comes from 1 SQL Statement.

Should I then create a table with the categories and try to link these 2 tables?

[mariussve1]

Yes, that is correct. To recognise the items with no values this needs to be a dimension connected to the fact table. Or else the values will be filtered out.

So Please create a dimension containing distinct values and connect it and then it will work.

Marius

[smlewisv]

SO, I created the dimention likr this:

I created the relation as well

ANd now is shoing the rows. Is there a way to replace blank spaces with 0?

[mariussve1 · Accepted solution]

Hi,

On the format pane under Rows where you have the column ServiceType, click on the ▼ to the right, before the X.

Then on the menu choose: Show items with no data:

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data

Br

Marius