Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Mwitting1
Frequent Visitor

Totals with SELECTEDVALUE measure

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 🙂 

1 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. 

View solution in original post

3 REPLIES 3
colacan
Resolver II
Resolver II

@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]

@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.

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. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.