Multi-Card information with different levels of summarization

[jam17]

I'm trying to build a single page report where stakeholders can look up an individual customer and see a combination of information about that specific customer and the larger account that customer belongs to.

Right now I am using a slicer to identify which customer to focus on.  From there I want to have a handful of cards (or a multi-row card) that display
1. Customer total sales
2. Customer Account Name
3. Account total sales

I started by trying to create a Results Table summarizing total sales by account, but I ALSO need to filter all this information by YEAR.  I have no trouble getting the Account Name to appear, nor any trouble with the Customer Total Sales to be affected by a YEAR filter.  However, I can't figure out how to have make it so that the Account Total Sales is influenced by it.

In other words, how can I make is so that the Account Total Sales information will a) reflect the correct account based on the Customer selected, and b) dynamically update when the YEAR Filter on the page is applied?

[Shravan133 · Accepted solution]

To achieve this, you'll need to create dynamic measures that can respond to both the Customer Slicer and the YEAR Filter on the report page. The key is to use CALCULATE and ALL functions to control how filtering is applied to the Account Total Sales while still maintaining the dynamic interaction with the selected Customer and YEAR.

Steps to Create the Dynamic Measures:

1. Customer Total Sales:
• This is a straightforward measure since it's already responding to the slicer and the year filter.

CustomerTotalSales =

SUM('Sales'[SalesAmount])

This will automatically adjust based on the Customer selected from the slicer and the YEAR filter applied on the page.

1. Customer Account Name:
• Assuming you have a relationship between your Customer Table and the Account Table, you can simply pull the Account Name for the selected Customer.

CustomerAccountName =

SELECTEDVALUE('CustomerTable'[AccountName])

1. Account Total Sales:
• This is where you need to ensure that the measure reflects the total sales for the entire account, based on the customer selected, and is filtered by the YEAR filter. You can achieve this by using the CALCULATE and ALL functions to remove the Customer filter but still apply the YEAR filter.

AccountTotalSales =

CALCULATE(

    SUM('Sales'[SalesAmount]),

    ALL('CustomerTable'[CustomerID])

)

Explanation:

• SUM('Sales'[SalesAmount]): This sums the sales.
• ALL('CustomerTable'[CustomerID]): This removes the filter on the CustomerID, so it sums the total sales for the entire account (not just the selected customer).
• However, since we’re not using ALL on the Date or Year columns, the measure will still be filtered by the YEAR filter on the page.
1. Putting It All Together:
• Now, create a visual (such as cards or multi-row cards) for each measure:
• Customer Total Sales: Display the sales for the selected customer.
• Customer Account Name: Show the account name for the selected customer.
• Account Total Sales: Display the total sales for the account the selected customer belongs to, while dynamically respecting the YEAR filter.

Visual Interaction:

• Slicer for Customer: Users can select a customer from the slicer, and all visuals will update to reflect information related to that customer.
• YEAR Filter: Apply a page-level filter or slicer for the YEAR so that the sales figures (both Customer Total Sales and Account Total Sales) are dynamically filtered by the year.

Alternate Approach: Using TREATAS (if needed)

If you have more complex relationships and need to ensure propagation across multiple tables, you could use TREATAS to control how filters propagate, but the above approach with CALCULATE and ALL should cover most scenarios.

[jam17]

Thank you!  I think the inherent lesson is here is that I didn't understand the differences between measures and columns and was trying to do everything at the column level, which was leading towards a potential answer of way too many relational tables I wanted to deal with.  Your explanation helped me see more how measures work.