How to show records without historical data?

[Smalfly]

Dear Community,

I am trying to create a table visual in Power BI that looks like this:

Product Group	Customer Sales	Similar customers Sales
Fruit	10	100
Vegetables	0	50
Dairy	5	20
...	...	...

with

• "similar customers" as an existing column in a customer table
• sales a measure in a sales table
• product group as an existing column in the product table

And 1 to many realtions between sales and the 2 other tables.

My report contains a filter on Customer. So basically, I select 1 customer, determine the Type of customer and then I calculate the sales of all the customers included in this type of customer. This is to determine which popular product categories have not been bought by the selected customer.

Both customer sales and similar customer sales work fine. However, when I filter for a specific customer, not all rows are shown in the table. So in the example above, the vegetables row disappears when I select a customer that never bought any vegetables. The problem is probably due to my calculation of similar customer sales:

Sales similar customers = 
var _CurCustomerType = SELECTEDVALUE('Sales'[Customer Type ID])

return CALCULATE(Sum('Sales'[Sales]), 
KEEPFILTERS('Sales'[Customer Type ID] = _CurCustomerType), 
ALL(Customer[Customer]))

Can anyone help me please in showing all product categories, regardless of whether a customer bought from a certain category in the past or not?

[Smalfly]

I found the difference. It is due to the orginal report having 2 column from product group in the table. I didn't realise that was important until now.

However, when I add the second column, the total remains correct and the vegetable line is even displayed but without a sales amount in it. I tried to combine ID and description into a concatenated field but that is not enough (as shown in the second table). Do you have any idea how I can make that sales amount displayed correctly?

[NickolajJessen]

Well, you mentionaed that jane doesn't have any vegetables sales, so there's not supposed to be any sales amount displayed, no?

[Smalfly]

Not a sales amount indeed, but customers similar to Jane (like Joe who is in the same customer type) bought vegetables so I would like to see a value for 'sales similar customers'. And the 'sales similar customers' is also not dispalyed. And this is while the sales Joe did, are taken into account for the sales similar customers total so somehow it is taken into account. Seems like it just not displayed.

[NickolajJessen · Accepted solution]

Check out your example file here:
https://drive.google.com/drive/folders/1AjePk7NUXAloXHr42BEa2lDOr5dQr2Wg

I'd also suggest you make consideration about where certain attributes belong. Is the product group an attribute of the sale or is it an attritube of the product? Is the customer type  and attribute of the sale of is it an attribute of the customer?

In the provided example i moved the customer type into the customer table. Consider removing the separate customer type table and the customer type id from the sales table.

[Smalfly]

@NickolajJessen That works. Thank you so much for your help!

You are 110% right on the number of attributes in the fact table by the way. I am aware of that but I do appreciate that you try to warn me for it.

[Smalfly]

Thank you for your reply @NickolajJessen!

I don't have a 'Show items with no data' option for my measure.

I did turn that option on for product group but that doesn't do the trick.

[NickolajJessen]

Allright, would i be correct in assuming you have your product group field in the sales table?
if that's the case, try bringing out the product, productgroup (and everything else related to product) out to a separate product Dimension table and do the "Show items with no data" on that

[Smalfly]

I do have a separate product group table. The product group in the report table comes from that separate table.

However, there is detailed product information in the sales table i.e. to be able to report on which products (not product groups) have been bought by a customer. That is also information I also display in my report so I cannot remove that from my sales table.

[NickolajJessen]

Are you able to share a .pbix or dummy .pbix through dropbox for your preferred sharing software?

[Smalfly]

Here's a link towards an example file: https://we.tl/t-d2m2VM0llO

[NickolajJessen]

Bruh 😂

[Smalfly]

Seriously? 😅

Now it 's up to me to find out why it works in the simplified example file and not in the original pbix 🙄 Thanks for you help!

[NickolajJessen]

Does checking the "Show items with no date" solve your problem?