SELECTEDVALUE based off of slicer value

[Anonymous]

Hello!

So I have four tables in my report. 

Sales Rep, Work Order Items, Commission Split, and Commission Split Detail. 

I'm trying to build a column in the work order items table called "%age of sale based off of the commission split table". 

I have set up the relationship so a Work order Item can share 2 or more sales reps based on the commission split / detail table.

If there is a split, the percentage has to be equal to 100%. 

However I need it to be able to filter the work order items table off of a single rep that I choose from a slicer since based on the Sales Rep taable since one item can be linked to more then one sales rep.  I also need to be able to build an accurate sales report per rep.

Lets say I choose Adam in the slicer, below is an example of what I want to happen:

Sales Rep Table
Sales Rep (Unique)
Adam
Gunther
Devon
Commission Split Table
Record ID (Unique)	Work Order #
5	10
8	11
10	12
Commission Split Detail Table
Record ID (unique)	Commission Split Record ID	Work Order #	Sales Rep	Split in Percentage
1000	5	10	Adam	75%
1001	5	10	Gunther	25%
1002	8	11	Adam	50%
1003	8	11	Gunther	25%
1004	8	11	Devon	25%
1005	10	12	Gunther	40%
1006	10	12	Devon	60%
Work Order Items Table
Item	Work Order # (Unique)	Commission Split Record ID	Cost	Sell	Commission Split # (Unique)	Column to populate based off of the sales rep I choose from the slicer. Will populate with the split in percentage
Window A	10	5	2000	5000	5	75%
Window B	10	5	1500	2000	5	75%
Window A	11	8	78	150	8	50%
Window C	12	10	500	1000	10	N/A because he is not on that commission split / detail so does not get credit.

Once I get the %age of sale in this column I will be able to multiply the cost and sell by this #age to get his credit for the sale.

I hope this makes sense.  I'm happy to further clarify if there is any confusion.  Thanks for the help!

[v-joesh-msft · Accepted solution]

Hi @Anonymous ,
First, you should know that the calculated column and calculate table can't be affected by any slicer. you could create a measure instead of a column.
Notice:
1. The calculated column/table does not support dynamic changed based on filter or slicer.
2. A measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

For your case, as long as the relationship between the tables is correctly created, you can directly use a table or matrix to achieve your corresponding results.

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ebt2WOSYB_FAhCkfIS2Gp8UBha5LAdVmKlMr_rw3dW-k1Q?e=Kj909D

Best Regards,

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

[Anonymous]

This worked great! however I'm having a new problem now.  I made a measure to multiply the sell * %age and that column worked in the table / matrix.  However at the total of the bottom of the column its giving me a 10x bigger number then it should.  Would you know why?

[v-joesh-msft]

Hi @Anonymous ,

This looks like a measure totals problem. Very common. See this post about it: 
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Best Regards,

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

[Anonymous]

thanks for all the help.  I'm now running into an issue where my report is only showing items that have a Commission Split Record ID.  Not every work order item will have a split if the sales rep is solely doing the work. the example above was not 100% accurate.  Please see new example below:

	Sales Rep Table
	Sales Rep (Unique)
	Adam
	Gunther
	Devon
	Commission Split Table
	Record ID (Unique)	Work Order #
	5	10
	8	11
	10	12
	Commission Split Detail Table
	Record ID (unique)	Commission Split Record ID	Work Order #	Sales Rep	Split in Percentage
	1000	5	10	Adam	75%
	1001	5	10	Gunther	25%
	1002	8	11	Adam	50%
	1003	8	11	Gunther	25%
	1004	8	11	Devon	25%
	1005	10	12	Gunther	40%
	1006	10	12	Devon	60%
	Work Order Items Table
	Item	Work Order # (Unique)	Commission Split Record ID	Cost	Sell	Commission Split # (Unique)	Column to populate based off of the sales rep I choose from the slicer. Will populate with the split in percentage
	Window A	10	5	2000	5000	5	75%
	Window B	10	5	1500	2000	5	75%
	Window A	11	8	78	150	8	50%
	Window C	12	10	500	1000	10	N/A because he is not on that commission split / detail so does not get credit.
	Window A	18	NONE	50	100		100%

The only Work around I can think of is to duplicate all queries and have one that has a commission split record ID and one that does not.

Does this make sense? Thanks

[v-joesh-msft]

Hi @Anonymous ,

Based on the created model, you need to concatenate the table "Commission Split Detail" and the table "Work Order Items" with the intermediate table "Commission Split" and use "Work Order #" as the unique field. Enter the corresponding record in each table. The results are as follows:

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EUW-LqBfKFJGh1pKl0EfFoQBA1VV-nDnY0FzWFnr59CI9Q?e=rdB5e6

Best Regards,

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

[Anonymous]

So thanks for the continued answers but I did not give you the proper data.  here is the proper data.

Sales Rep Table
Sales Rep (Unique)
Adam
Gunther
Devon
Commission Split Table
Record ID (Unique)	Work Order #
5	10
8	11
10	12
Commission Split Detail Table
Record ID (unique)	Commission Split Record ID	Work Order #	Sales Rep to split	Split in Percentage
1000	5	10	Adam	75%
1001	5	10	Gunther	25%
1002	8	11	Adam	50%
1003	8	11	Gunther	25%
1004	8	11	Devon	25%
1005	10	12	Gunther	40%
1006	10	12	Devon	60%
Work Order Table
Record ID	Work Order Main Sales Rep
10	Gunther
11	Adam
12	Gunther
18	Adam
Work Order Items Table
Item	Work Order # (Unique)	Wrok Order Main Sales Rep (Linked)	Sales Rep to split	Commission Split Record ID	Cost	Sell	Commission Split # (Unique)	Column to populate based off of the sales rep I choose from the slicer. Will populate with the split in percentage
Window A	10	Gunther	Adam	5	2000	5000	5	75%	should show up because he is on the commission split.
Window B	10	Gunther	Adam	5	1500	2000	5	75%	should show up because he is on the commission split.
Window A	11	Adam	Adam	8	78	150	8	50%	should show up since he is main sales rep and on the commission split
Window C	12	Gunther	NONE	NONE	500	1000	NONE	NONE	SHOULD NOT SHOW UP BECAUSE HE IS NOT THE MAIN SALES REP OR SALES REP TO SPLIT
Window A	18	Adam	NONE	NONE	50	100	NONE	100%	should show up because he is the main sales rep of the work order / all items.

For every work order, there is a MAIN SALES REP.  This person is responsible for doing most of the work.  There can also be a commission split i.e. a Work Order can be split between 2 or more sales reps as long as the percentage is equal to 100%/

So the two scenarios are 

a Work order with no commission split.  So just the MAIN SALES REP gets credit for the work order.

a Work order with a commission split.  All Sales rep(s) in the commission split will get their %age credit for the work order.

If I choose Adam from the data above I want it so show as this:

Work Order Items Table
Item	Work Order # (Unique)	Wrok Order Main Sales Rep (Linked)	Sales Rep to split	Commission Split Record ID	Cost	Sell	Commission Split # (Unique)	Column to populate based off of the sales rep I choose from the slicer. Will populate with the split in percentage
Window A	10	Gunther	Adam	5	2000	5000	5	75%
Window B	10	Gunther	Adam	5	1500	2000	5	75%
Window A	11	Adam	Adam	8	78	150	8	50%
Window A	18	Adam	NONE	NONE	50	100	NONE	100%

on work order 10, gunther is the MAIN SALES REP, but adam is on the comission split for the work order.  So when I choose adam from the sales rep slicer i want the commission percentage to show as 75%% for these items. on work order 11 he is the MAIN SALES REP and on a commission split, so he would get his %age of the work order from the commission split i.e 50%.  On Work Order 18, he is the MAIN SALES REP and there is no commission split for this work order.  So when I choose him, all work order items for work order 18 need to show up as 100%.

Is this possible with how the data is setup?