Sales vs. Targets on many-to-many connection

[AnkaBeek]

Hello, 

 I am dealing with the following problem in an analyse for each SalesPerson per ProductGroup the SalesAmount vs.TargetAmount

1. Targets for current year are provided in an excel list with the following structure:

Name Target_ProductGroup Target_Amount
SalesPersons1 ProductGroup1 Amount
SalesPersons1 ProductGroup2 Amount
SalesPersons1 ProductGroup3 Amount
SalesPersons1 ProductGroup4 Amount
SalesPersons2 ProductGroup1 Amount
SalesPersons2 ProductGroup2 Amount
SalesPersons2 ProductGroup3 Amount
SalesPersons2 ProductGroup4 Amount

1. Sales (source API), the table has multiple columns:Name              Product     Sales Amount  Date(*)  Customer  ...
   SalesPersons1 Product1 Amount
   SalesPersons2 Product2 Amount

(*)includes sales from previous years

What I did (A):

-created a new table in Power Bi with the Targets (avoid import of an excel due to later problems with Scheduled Refresh on Power BI server, I am not sure if it is correct, this is my conclusion)

-in the Sales table, Group by the product to create the ProductGroup

-create a connection between Targets and Sales based on Name column , but is a relation many-to-many.

When created a Pivot table it shows the SUM of TargetAmount for each productgroep.Could be because the many-to-many relation but I don’t know how to solve it.

What I tried is a kind of work around.

In the Sales table, add New column (see formula below) to bring the targets. It works but not 100% correct. Except the amount of work to bring all the Target Amount, the problem is that when a sales person has no sales for a ProductGroup, the TargetAmount is not showed.

Target = SWITCH( TRUE(), AND('salesdetails’[accountmanager] = " SalesPerson1", 'salesdetails? '[Targets (splitext_groepen)]= " ProductGroup1"), "25000", AND('salesdetails'[accountmanager] = " SalesPerson1", 'salesdetails’ [Targets (splitext_groepen)] = " ProductGroup2"), "5000", AND…… so on to fill in all the ProductGroup and all the SalesPerson.

Please advise what could be the correct approach to be able  to analyse sales vs. targets in this context. Would be great if you could suggested what functions are suitable for this type of analyse.

Many thanks.

A.B.

[v-xicai · Accepted solution]

Hi @AnkaBeek ,

You may create column in table Sales like DAX below.

Target_Amount= CALCULATE (FIRSTNONBLANK ( Targets[Target_Amount], 1 ),FILTER (Targets, Targets[Name] = Sales[SalesPerson] &&Targets[Target_ProductGroup] = Sales[ProductGrou] ))  

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.