Limiting database data using an Excel table in Power BI

[RobertHaas]

I have sales data retrieved from a Teradata database via an ODBC connection on my workstation.  I'm using the Model view to join my Sales Fact table to my Item Dimension table using a product key. with no issues.  I would like to have the flexibility to show my data in Power BI using the database fields, but also have the ability to limit the data for a visual by using an Excel worksheet. 

I have brought in an Excel Worksheet with only the Top 6 Department IDs.  I would like to join the Excel Data with the Item Dimension data using the Dept_ID as a common key.  This way the relationship is SalesFact-to ItemDimension-to Excel.  I did this in the Model View with no issue using simple joins.

When I create a table pulling the Dept_ID from the Excel, the Dept_Description from the Item Dimension table and the Sales Dollars from the Sales Fact, the table correctly limits the data to the 6 Department in the spreadsheet. The indididual sales amounts for each line in the table is correct.

BUT the grand total at the bottom of the table is the total of all departments not just the 6 lines displayed in the table itself.

What can I do to get the grand total to match the data displayed in the table?

Bob   

[v-pnaroju-msft]

Thank you, @amitchandak , @lbendlin , and @MasonMA , for your responses.

Hi RobertHaas,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

Thank you for sharing your update. We are glad that you found the solution and were able to get the expected result. 

Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[MasonMA]

@RobertHaas 

Hi, if this is the only one visual you are building, you may add a visual-level filter on 'Item Dimension'[Dept_ID] where the values match Dept_ID from the Excel table.

[lbendlin]

You seem to be using implicit measures (letting Power BI do the aggregation for you).  In such scenarios it is better to have measures that explicitly calculate the desired sum.

[amitchandak]

@RobertHaas , is there a filter/slicer on page? What measure is used? This can measure issue if isinscope or isfiltered has been used. 

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[RobertHaas]

This Highlights The issue I'm seeing.  The 6 department IDs are displayed in the table, and the Amounts displayed for each department is correct.  The Grand Total displayed is approximately 325K which is the Total of all the Departments,  not just the 6 I'm filtering using the Excel data to limit the data displayed.  The total for the 6 departments displayed should be approximately 215.5K.

No Filters/Sliders are on the page.  The only filters I'm using are at the "Filters on All Pages".  

Again the Model has the Teradata Sales Table joined to the Teradata Item table using a sigle item number key.  The Teradata Item table is joined to the Excel Data within Power Bi using a single join between a Department ID Key in each. 

Thanks very much for your help!

Bob

[RobertHaas]

I think I found a solution.  I noticed the visual Filter area said "ALL", So I added an addition filter where the Excel ID was not blank.  This altered the total to the 215.5K I was expecting.

Thanks very much for you looking into my question.