Measure changing when column added to matrix

[AnthonyH28]

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


Broken Image

[AnthonyH28]

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

Here is the model

[AnthonyH28]

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]

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

[AnthonyH28]

'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?

[AllisonKennedy]

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