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
AnthonyH28
Helper I
Helper I

Measure changing when column added to matrix

This inquiry is to understand why this measure is being affected by another column being added.

I have a list of products with measures for Past Year, Past YTD, YTD and YTD Growth for sales - pretty standard.
I have a measure that is calculating the % of Sales for each product. Here is that code:

Percentage of Sales = 
VAR Volume =
    'Sales Metrics'[Sales Combined YTD]
VAR AllVolume =
    CALCULATE ( 'Sales Metrics'[Sales Combined YTD], ALL ( DIM_Products[Product] ) )

RETURN
    DIVIDE ( Volume, AllVolume )

 

It works great when I add it to the above list of information.

Now, I was tasked with adding another column that will show the same kind of information (Percent of Sales) but instead of it being based on the Slicer (in this case, the customer), it needs to be based on our companies sales. The idea that we know how well our products sell and if we see someone not buying one of our best products, its a sales opportunity.

So what I did was create a new table in SQL with all of our products and then each day I update the Percent of total sales for each product and put it into the corresponding column.

Back in PowerBI, I link this table to my Products table using the ProductKey. I then add the column (its not a measure, because no measure is needed) to the matrix and when I do, the Percent of Sales Measure I listed above - breaks. It stops working correctly. Everything under Percent of Sales goes to 100% and the Percent of Company comes in correctly. If I remove Percent of Company, then Percent of Sales goes back to what it should be. Ive attached 2 images, showing before and after.

What is causing this in that above measure that when I add this, it breaks it? Is it because of the relationship to that table? And if so, how do I modify that measure to not break when I add this?

Working Image
product_working.PNG

Broken Image
product_not_working.PNG

5 REPLIES 5
AnthonyH28
Helper I
Helper I

The related worked in that it brought in the right information, but that measure still lists everything as 100%.

Here is the model

AnthonyH28_0-1605909500110.png

 

Let me reclarify, my last statement I crossed my words.


DIM_Products is my sku level table, about 17,000 skus or so. The Table that has the Percent of Company (DIM_Products%Company) is the much smaller table, as we have about 60 products. Those 60 get updated every day with a new % figure in their corresponding field in their row.

I brought the column over from DIM_Products%Company to DIM_Products using Related but the issue persists.

I supposed this actually would be easy enough to just replicate the RELATED function in SQL before the data is ingested into PBI, which solves my relationship problem because I can just get rid of that table all together. But I still am interested how to fix it in PBI.

EDIT: I did just that. I recreated the RELATED function in SQL since I could not get it to work properly in PowerBI. If someone has the right solution, I will update this with that information. I welcome a PowerBI fix since this didnt really feel like it needed to be solved in SQL but I did.

AllisonKennedy
Super User
Super User

@AnthonyH28 Can you share the relationships between all tables involved please? Just to clarify,  'Sales Metrics'[Sales Combined YTD] is a column, correct? Please add that to the same visual with [Percent of Comany] (which is also a column, right? What table is it in?) and see if that helps you see what's going on a bit better, but yes it will be relationships between Sales Metrics and Percent of Company table. My guess is that Dim_Product to Percent of Company is 1 to many with single cross filter direction? This can't talk to/filter Sales Metrics.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

'Sales Metrics'[Sales Combined YTD] is another measure. Its actually in both photos, just renamed. That would be the YTD column.

Yes, the relationship is as you described. The DIM_Product is everything at sku level where this new one I made is one level up, at a product level. We have many different skus per product. So yes, from DIM_Product to Percent of Company, its Many to 1 with single cross filter.


Would it be better if I just recreate the table that has the Percent of Sales down at the SKU level so its a 1:1?

@AnthonyH28 You don't need to recreate the table, just use RELATED([Percent of Sales]) to add it to the SKU Level. Then use that new calculated column instead. If that doesn't work, share a screenshot of your model view so I can visualize it better not in my head. 😆


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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.