Part of column equation is based on slicer selection

[CUDataArchitect]

I have 2 matrix in PowerBI: Current Costs per Month and Processing Offers.

The Current Costs per month is set up like this:            The Processing Offers looks like this:

                             Cost                                                                              Monthly   Term       Savings

Category A            $                                                         Vendor A              $              $            %

Category B            $                                                         Vendor B               $              $            %

Category C            $                                                         Vendor C              $              $            %

-------------------------

Total                     $$

I am having trouble calculating the "savings" column in the Processing Offers table. The "savings" would be calculated by taking the Monthly $ From each Vendor, subtracting the current Total $$ From the Current Cost table (that may or may not be the same Vendor) Then dividing by the Current Montly Cost Total ($$) To get a Percentage of Savings. The point of these Matrix are to show what their costs are currently and what their cost savings would be if they considered the Offer from the new vendors. 

There is a slicer on the page for the Invoice Date that is tied to the "current Costs". There are many different invoices in the database and I want the end user to be able to select which invoice they want to compare. 

I am used to the SSRS Report Builder. In that program I would have saved the Total Currenty Monthly Cost ($$) as a Parameter value but that doesn't seem to work in Power BI. Is there a way that I can store that Current Monthly Cost Value (that is filtered by the Slicer) and apply that to the Processing Offer Matrix as a part of the equation for the "savings"? The Slicer selection does not apply to the Processing Offer Matrix. 

How can I grab that Total value from the first matrix and apply just that value to a part of the equation in an unrelated matrix? 

[CUDataArchitect · Accepted solution]

I ended up solving this myself. I was able to calculate the "Savings" by taking my origional single dataset and breaking it out into 3 different datasets. The first was for the "current costs", the second was for the "processing offers" and the third was for the client name (a slicer that I used that was not mentioned in the origional post). 

When I created the slicer for the "invoice date" I used the "current costs" dataset. I needed the "client name" slicer selection to filter both datasets so I created a relationship between all 3 datasets. The slicer was then created from the "client name" dataset. 

When creating the calculation for savings, I used the following measure: 

Savings = DIVIDE((SUM(CurrentCosts[NetAmount]) -SUM(ProcessingOffers[MonthlyNetAmount])),SUM(CurrentCosts[NetAmount]))

The current costs amount was filtered to only the selected InvoiceData because the slicer was filtering all data in the dataset. The Processing Offers amount was not affected by the slicer because the slicer was not attached to that dataset. 

[v-juanli-msft]

Hi @CUDataArchitect

Based on my understanding:

1.You want to show two matrixes which may be from two different datasets and have no relationships.

2.For first matrix, you want to get the total of current cost based on the selection in the slicer.

    the total of current cost is also referred to “the current Total $$” or “Current Montly Cost Total ($$)” in your description, right?

3.For second matrix, calculate “savings” with the equation

   “(Monthly $- the current Total $$)/ the Current Montly Cost Total ($$)”.

So we can get this

This can be achieved by two measures:

Cost Total =
CALCULATE ( SUM ( Sheet1[Cost] ), ALLEXCEPT ( Sheet1, Sheet1[Invoice Date] ) )

Savings =
DIVIDE ( MAX ( [Monthly] ) - [Cost Total], [Cost Total] )

Best Regards

Maggie

[CUDataArchitect]

@v-juanli-msft Thank you very much for your assistance with this!

When I use the measures you provided I am still running into an issue:

The second Matrix (Looks like this in your example)

                       Monthly    Term        Savings

Vendor A          14            1               .56

Vendor B          15             2              .67

Vendor C          16            3              .78

This matrix is not tied to the InvoiceDate Slicer. The "Monthly" Amount of 14,15,16 are not affected when a different invoice date is selected in the slicer. So when I use the measure 

Cost Total =
CALCULATE ( SUM ( Sheet1[Cost] ), ALLEXCEPT ( Sheet1, Sheet1[Invoice Date] ) )

In this matrix, the number does not match the First Matrix. Because the first Matrix IS affected by the InvoiceDate. So instead of matching the "9" as the cost total it is showing as "500" for example. 

If I try to attach the Slicer to the second matrix and calculate each "monthly" and "term" amount to not include that slicer selection it will no longer display all Vendors. What is the best way to proceed? Is there a way to get the measure for Cost Total to INCLUDE the slicer selection - but just the measure and not the whole Matrix?