Matrix - repeating sum value for secondary row with two tables

[sunnybono]

Hi All,

Hopefully I have missed something really simple here but I can't seem to get my head around this error.

I have two tables, one with sales data, one with quote data. Both are fairly straight forward in that they have columns as follows

Sales table

Date

Reseller

part code

value of sale

qty

Brand 

Quote table

Customer

part code

value of quote

Brand

qty

date

Customer and reseller are the same data, but have different names in each table.

If I create a many to many bi directional relation on either brand, customer/reseller/partcode and create a matrix that shows

- Reseller

- Brand

For both I want to view total sales and also total quoted value 

The top level sum, ie sum of both total quote and sales is correct at reseller level, however once expanded to brand the value repeats.

Thanks

[sunnybono]

Thanks for the reply. 

Yes, both tables have a date field. I have added a calendar table, however I hadn't intended on using that yet as I am only looking at a subset of data. I created a distinct partcode table however I am not sure how that factors in as a dimension? 

I can't see how I can avoid using a many to many relationship in this instance?

[TobyNye]

A dimension table is just a table that contains values you wish to use in relationships and filter conditions, fact tables store your actual data. I would suggest setting your subset data model to look something like the below:

However, I realised after making this that it still won't be able to split out your quotes by reseller because there is no reseller field within the quote table (or at least not as far as I can see from your post). If it does exist in there you can use the same method with part code for the reseller, if not then your quotes do not have an amount that is respective to particular resellers. However, depending on the fields within your table and what values are the same across the two tables, you may be able to perform operations in power query or SQL (assuming that your data is an SQL database) to create an identity column that is unique in at least one of the tables that you can use for the relationship in power bi. I'd need a full look at your data fields with some dummy sample data to be able to advise further. 

[sunnybono]

That makes more sense. Was struggling to get my head around it. 

I can't upload my actual data so have created a duplicate which demostrates the error even when trying to copy the above model & relationships. 

https://file.io/kZcfsTxIIGtk

[Ashish_Mathur]

Hi,

There is no file there.  Share the download link again.

[sunnybono]

https://file.io/2s7IjH8R6Lqt

Hopefully this one works. 

[Ashish_Mathur · Accepted solution]

Hi,

You need to create 2 seperate DIM tables - Brand and Reseller.  Create relationships (Many to One and single) from the Quotes and Sales tables to the 2 DIM tables.  To our visual, drag Brand and Reseller to from the DIM tables.

[sunnybono]

Thanks for your help. 

I found some blank values in my real data as well that wouldn't allow for many to one relationships so had to filter those out, but now works perfect. 

[sunnybono]

Can anybody spot any obvious errors in this report? Any pointers appreciated. 

[TobyNye]

I think you'd be best served creating some dimension tables to avoid many to many relationships. They cause strange behaviour and should generally be avoided. I'm assuming that part code would be the identifier here and also that there is a date field in your sales table. If you create a date table (you can use CALENDARAUTO() at the most basic level or look something up and make something nicer) and a table as below:

Part Codes = DISTINCT('Quotes'[PartCode])

That should give you tables that you can link to both using 1 to many relationships, then use the dimension table in your visual for part code and date. This should resolve the issues that you're experiencing as they are a result of the many to many relationship not fully understanding the join context of the two tables. Hope this helps, let me know if you have any further issues/questions.