How to link target table and sales table using Bridge ?

[admin11]

Hi All

I need to create sales target for my sales team. 

Step 1 :- I create Target Table as below :-

SOURCE	date_t	GROUP_16	ODSE_target_sales_16
TS	01-Jan-20	MAX	66,666.00
TS	01-Feb-20	MAX	66,666.00
TS	01-Mar-20	MAX	66,666.00

Step 2 i import the Target table to PBI :-

Step 3 I link the Target Table Date with master Calendar date . not issue. The issue come when i try to link the Sales Staff from Sales Table to GROUP_16 from Target Table. I get Relationship many-many.

STep 4 When i compute Sales vs target , i get below problem on sales amount wrong , as it cannot have same amount :-

Step 5 I have create a bridge table , but the amount from sales table still not display correctly at Target table :-

Hope some one can advise me.

Paul

[v-janeyg-msft · Accepted solution]

Hi, @admin11 

According to your requirement, I modify your bridge table code, then you can put the bridge table field 'staff' in the table1's value.

Like this:

Group16 = 
// DISTINCT (
//     UNION (
//         SUMMARIZE ( SALES,  SALES[Sales Staff] ),
//         SUMMARIZE ( TARGET_16, Target_16[GROUP_16])
//     )
// )
INTERSECT(DISTINCT(SALES[Sales Staff]),DISTINCT(Target_16[GROUP_16]))

Best Regards

Janey Guo

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

[admin11]

@v-janeyg-msft 

WOW ,  you are very expert in link the table.

many thank

Paul

[v-janeyg-msft]

Hi, @admin11 

Thank you for your praise, I just try my best to realize your needs from your perspective. Can you give me kudos as a reward😊

Best Regards

Janey Guo

[admin11]

@v-janeyg-msft 

Done, i Owe you one drink.

[v-janeyg-msft]

Hi, @admin11 

Have your problem been solved? The shared file has been deleted. It's hard to judge by just view the screenshots, but it should be a problem between relationships. Many-to-many relationships are prone to problems. 

If you still need help, please feel free to ask me.

Best Regards

Janey Guo

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

[admin11]

@v-janeyg-msft 

Thank you for your sharing , i still unable to figure out how to solve my issue , below link is my PBI file :-

https://www.dropbox.com/s/yusevz6gyssb2po/PBT_V2021_371%20sample%20data%20v002%20link%20sales%20and%...

Paul

[PhilipTreacy]

@admin11 

Hi Paul,

I'm not sure what your question is.  It looks like you already have the table in PBI.  How do you want this target created?  What are the targets?  For what periods?  How do you want the targets displayed?

Regards

Phil