Forum Discussion
SELECTEDVALUE based off of slicer value
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:
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.
Solution SageHi 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:
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.
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?