Report not capturing all relevant data

[sgannon1]

Hi all, 

Apologies if my question is a bit long winded, it's a little difficult to explain my issue. 

I imported 6 tables from sql for my report, each giving different information on accounts activity (invoice details, products purchased, contact details, credit note details...).  I'm trying to display revenue year to date by salesperson last year vs this year, and this covers about 2000 individual accounts. 

The problem is that some accounts may have been assigned to a different salesperson last year, so when I run the report it is giving me back 'incorrect' information. (I want the value for an account to go towards the salesperson that it is assigned to right now).  

From what I can see, the system is looking up the invoice number, and the salesperson that is associated with that. I can't figure out a way to look up the account and which salesperson is currently assigned to it(I don't care who was in charge of the account for a certain invoice, I'm only interested in who is assigned to it now). 

Would anybody have any suggestions?  Thanks in advance!

[sdjensen]

You should have add a table with all you accounts to you model and this table should also hold the current salesperson assigned to the account. Then create links from you table with your values to the account table and when you use salesperson from your account table in a visual this will show your values for each current salesperson to the account.

[sgannon1]

Thanks for your suggestion.

I already have a separate table imported with current salesperson and am using this when building my report. However the report still seems to be looking up the salesperson that is associated with an invoice, and not the one that is currently assigned to an account.  

When I created the relationships between the tables I used invoice number as this was the only common trait. Could this be the reason it is now looking up salesperson by invoice?

[v-qiuyu-msft]

Hi @sgannon1,

In your scenario, all the data display in the report depends on the relationship between data tables. You can drag salesperson, invoice and account in a table visual to see what's the relationship between those fields, then you can know how to improve the data model. If you prefer, you can share some sample data and expected results for our analysis.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[sgannon1]

Hi @v-qiuyu-msft,

I have attached a file showing current relationships that I created between the tables.  The second relationship (Document No and No) is relating to invoice numbers. This is the only common trait between these tables so I don't think there is anything else I can link them with. 

The contact table (from the first relationship) is the table that has the current salesperson details, so I have created a relationship between that and the sales invoice header (which has invoice details). 

Sales Invoice line has the actual amount that was invoiced. 

The sales invoice header table (below) also has details of the salesperson associated with the invoice, so I think this is still being picked up when I create reports.

.

Relationship Table:

Contact table showing current salesperson assigned to account -

[sdjensen]

@sgannon1 - This look like Dynamics NAV data and I know that database very well - I have been building BI models on top of Dynamics NAV for 10 years.

1. Why do you go though Sales Invoice Header to link to Contact? You have the Sell-to and Bill-to customer right there on the Sales Invoice Line. ?

2. Do your customer numbers match your contact numbers? else you would need to create a relationship to you customer table instead of your contact table.

3. With the current relationships if you add Salesperson from your contact table to your visual and your invoice amount from Sales Invoice Line then this will be split by the current Salesperson from the contact if your customer numbers match your contact numbers.

Attached screenshot is from a model I build on Dynamics NAV demo data - all measure names and dimensions is in danish, but it basically show Revenue, Gross Profit and Gross Margin per month, year to date, last year to date, by country, customer and item. The model also offer the option to split the sales measures by sales person either the sales person from the time of the transaction or current sales person attached to the customer.

4. Why don't you use your customer table instead of contact table? customer table should also have the current salesperson and then create a direct connection from Sales Invoice Line to Customer?

[sgannon1]

Hi @sdjensen,

Thanks so much for your help. Yes you're right it is Navision data. I am very much a beginner to working with databases and Power BI, but you have really helped. I replaced the contact table with the customer one, and linked this to the sales invoice header, and it seems to be capturing the data correctly now.

I also need to add another table to my data (I was planning to append the table), but the currency is currently in GBP and I need to convert to EURO. Do you have experience of doing this?

Thanks again for your help.

[sdjensen · Accepted solution]

I have build SSAS multidimentional models with implemented currency conversion, but I still haven't done this in DAX.

However what you should need is you Currency Exch Rate table where (if data exists in your Dynamics NAV) you should be able to find your exchangerate with a from date. I am guessing you source data have some sort of posting date, so you need some DAX code that will lookup the exchangerate that was current when the transaction was made and then multiply with your amount. If you append this table to you already existing one then you should properly need an exchange rate (= 1 assuming that you current date is EURO) for this data too or your measure will break.

I think you should try to open a new thread with your currency problem and provide a good description of your data then I am sure some of the DAX experts here will be able to provide you with a formula. 

[sgannon1]

@sdjensen

thanks for yor help. I will do that! 

[sdjensen]

@sgannon1 I would guess that is exactly why. I would create the account table with their current salesperson and then create the link with account and not salesperson - if you link with salesperson you will get your values split by the salesperson at the time the transaction was created.