Help Calculating the Difference Between Two Normalized Columns

[dpeterson99]

I have a raw data table that holds information about the inventory of two different retail stores. It includes 4 columns:

Column 1: the unique ID of the inventory item

Column 2: the category of the inventory item (clothing, electronics, toys or food)

Column 3: the relative size (%) of the item in Store A's inventory

Column 4: the relative size (%) of the item in Store B's inventory

The two stores have some overlapping inventory, but not every item in Store A's inventory is in Store B's inventory (and vice versa). Therefore, there can be values of zero in Columns 3 and 4.

In the Power BI Desktop app, I imported this raw data table and then created a Table widget that shows the relative size of each inventory category for each store, as well as the difference in size between the two stores. To do this, I simply dragged Columns 2, 3 and 4 into my table and then created a Custom Measure to calculate the difference (see DAX expression below):

Difference = SUM([Column 3]) - SUM([Column 4])

The final table came out as expected and looks something like this (recreated in Excel for privacy reasons):

I then decided I wanted the ability to filter by inventory category. That way I could filter to Food and Toys, for example, before renormalizing the sizes and viewing the difference in inventory makeup between the two stores. I added a Filter widget to my Power BI, set the "Store A Inventory %" and "Store B Inventory %" columns to display as a % of the Column Total, and then tested it out. To my dismay, this was the result (again reproduced in Excel):

Although the first two columns were renormalized to 100% as expected, the Difference column is still referencing the underlying data and isn't aware that I have renormalized the columns it is supposed to be showing the difference of. The expected output would have been:

Displaying the difference column as a % of the Grand or Column Total is not a solution because this would simply show the proportion between the two rows. What I need is a way to simply and directly calculate the difference between the two columns in the Table, as opposed to referencing the raw data which obviously doesn't change when filters are applied. It seems like something that should be very easy to do, but I have yet to find a solution. Any tips/suggestions/questions are welcome.

Thanks!

[Anonymous · Accepted solution]

Hi @dpeterson99 ,

According to your statement, I think your table should look like as below.

M_Store A of inventory % = 
VAR _CURRENTVALUE =
    CALCULATE ( SUM ( 'Table'[Store A of inventory %] ) )
VAR _TOTAL =
    CALCULATE ( SUM ( 'Table'[Store A of inventory %] ), ALLSELECTED ( 'Table' ) )
RETURN
    DIVIDE ( _CURRENTVALUE, _TOTAL )

M_Store B of inventory % = 
VAR _CURRENTVALUE =
    CALCULATE ( SUM ( 'Table'[Store B of inventory %] ) )
VAR _TOTAL =
    CALCULATE ( SUM ( 'Table'[Store B of inventory %] ), ALLSELECTED ( 'Table' ) )
RETURN
    DIVIDE ( _CURRENTVALUE, _TOTAL )

Difference = [M_Store A of inventory %] - [M_Store B of inventory %]

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dpeterson99]

This is exactly what I was looking for! Thanks so much!