Totals with SELECTEDVALUE measure

[Mwitting1]

Hi all,

I was asked to create a matrix that starts of by showing the items that are in MyItems, as being the base. Then I want to see all the items that exist within the group from the linking table. Then finally I need to show the margin based on the sales of those item numbers from the sales table. 

The quantity sold in the sales table, however, has to be divided by the content type as reported in the MyItems table, in order to calculate the margin as reported in MyItems table. The problem here was that there can be two items with 2 content types within one single group. This was solved by using the SELECTEDVALUE function:

Packages sold measured in my content = SUM(Sales[Quanitty sold]) / SELECTEDVALUE(MyItems[Content])

My Margin = [Packages sold measured in my content] * SELECTEDVALUE(MyItems[Margin]) 

Now the matrix shows MyItem numbers first, then looks for all the items that exist within the group in the linking table. Next it looks up the quantity sold for those items in the sales table and then calculates the amount of packages sold as well as total margin based on the item number in the assortment table (items from the linking table are included in both groups, but this is the expected result). 

The last step in this puzzle now, is to include the total margin in the totals. This now returns blank as technically all items are selected. Here is the pbix: https://gofile.io/d/FTpC2L 

Any help would be greatly appreciated 🙂 

[colacan]

@Mwitting1 

I recommend not to introduce Liking table.

1. Cconnect MyItem and Sales by Item number since Item number is unique in both tables (or at least 'one to many' relationship)

2. Creat colculated column of MyItem[Adjusted_Margin] = [Margin]/[Content]

[Mwitting1]

@colacan 

Thank you for the suggestion!

It is not possible to remove it as this is the only way to go from the MyItems, to the group number (while keeping the expected duplicate values). 

I read posts about using ISFILTERED function but I had no success with that yet.

[Mwitting1 · Accepted solution]

Alright, as a temporary solution I have joined the linking and MyItems table to get the first steps of the matrix out of a single table. Now the MyItems Content no longer has to be found with selectedvalue.