Slicing calculated measure based on the user's filter choice

jennifer zen 341 Reputation points
2022-05-27T08:20:59.983+00:00

Hi,

I have a dimension table and two fact tables --dim.customer, fact.sales_district (on district level), fact.sales_national (national level).

I have a measure -->NationalTotalCost:=SUM(sales_national[National_cost])

I want to able to slice this measure based on the user's selection on filter. Say on excel/powerpivot , user selects filter on cutomer code then the measures should be able to slice the measure on the code level(the yellow higlighted part should change) but the grand total will remain the total sum (pic below for an idea).

206048-screenshot-2022-05-18-105358.jpg205989-screenshot-2022-05-27-091839.jpg

I have an idea of using ISFILTERED and SWITCH for the user selection but what about the slicing part?
Is that possible through DAX? I can't create a relationship between the dim.customer and fact.sales_national (national level) as the path will be ambiguous.

Any advise is appreciated.

Thanks a lot

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,644 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,242 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 32,426 Reputation points
    2022-05-30T01:55:58.937+00:00

    Hi @jennifer zen ,

    One of the easiest way is to make it Bi-directional for sales_district and sales_national.

    For more detailed information， please refer to official document：

    https://learn.microsoft.com/en-us/analysis-services/tabular-models/bi-directional-cross-filters-tabular-models-analysis-services?view=sql-analysis-services-2022

    Bi-directional cross filters in Analysis Services tabular models

    Learn how bi-directional cross filters in tabular models eliminate the need for hand-crafted DAX workarounds for propagating filter context across table relationships.

    Regards，

    Zoe

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  2. Alexei Stoyanovsky 3,411 Reputation points
    2022-05-31T06:21:45.71+00:00

    Ideally, your national level sales should be the sum of district level data, which would make the sales_national table redundant. If the totals do not match and you chose to have both facts, you should first remodel, adding time and product 'dimension' tables, removing the relationship between your sales tables, and doing the correct relationship between sales_national and the customer dimension data (M:M on company_code, or separating company into a dedicated table).
    For your 'grand total will remain the total sum' part, see whether the 'include filtered items in totals' pivot table option works for you.
    Note that currently, your sales_national table doesn't have sales information at customer granularity, being aggregated to company level. The best you can do is an estimation perhaps utilizing data in the district-level table. Since you mentioned file sources, this is best done while loading the data into the tabular model.

    0 comments