Measure to sum table based on another table and slicer

[AndyDo]

Hi,

I have 3 tables:

Table 1 - Transfers Table

Table 2 - Inventory Transactions Table

Table 3 - Locations Table

I want to create a measure in Table 1. For each row, it will take the item no., and look at Table 2 - Inventory Transactions Table and sum the quantity column for that item. Essentially I want to sum the quantity i purchased of this item. But I want to sum the rows in table 2 based on a slicer. The slicer is using table 3 - locations table.

For example - if in the slicer i select location code = Main. The measure will look like this:

I can figure out how to do this with a calculated column, but the problem with using a calculated column is that I can't use the slicer as a locations filter.

Thanks!

[Anonymous]

Hi @AndyDo ,

First of all, many thanks to @miTutorials  for your very quick and effective replies, and I will give some additions below:

1.Create the simple table.

2.Create the relationships between three tables.

3.Create the new measure to calculate sum based on slicer.

Sum the item no inventory Transaction = 
CALCULATE(
    SUM('Inventory Transactions Table'[Quantity]),
    FILTER(
        'Inventory Transactions Table',
        'Inventory Transactions Table'[Item No] = SELECTEDVALUE('Transfers Table'[Item No]) &&
        'Inventory Transactions Table'[Location Code] IN VALUES('Locations Table'[Location Code])
    )
)

4.Drag the measure into the Transfers table visual. The result is shown below.

Best Regards,

Wisdom Wu

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

[AndyDo]

Thank you. It seems if I do that the slicer will not affect the measure calculation anymore though. 

[AndyDo]

Thanks v-jiewu-msft. That works. The problem is that if I have a location slicer, and I select a location such as EAST, then my table visual will filter the rows as well. Is it possible for this slicer to not filter the table, but only the measure thats displayed in the table? Thanks!

[Anonymous]

Hi @AndyDo ,

Based on my testing again, it is impossible to filter only the measure without filtering the table when selected the location in the slicer visual.

please try the following methods as workaround:

Try to use Edit interactions.

Then, click on this button.

You can also view the following document to learn more about slicer visual.

Slicers in Power BI - Power BI | Microsoft Learn

Change how visuals interact in a report - Power BI | Microsoft Learn

Add a filter to a report in Power BI - Power BI | Microsoft Learn

Best Regards,

Wisdom Wu

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

[miTutorials]

Have you tried to create relationships betweeen the tables and create a slicer ?