Sum 2 filtered visuals (not using relationship)

[DHorsley1]

I have filtered 2 visuals (tables) using slicers (coming from the same data source, and I have had to edit the slicers interactions to be able to see the results I want. ). I have then created a sum 'adj. book of business' that is used for both tables. I want to sum both filtered visuals 'adj book of business' values, but it is not letting me do so. Can anyone help with a formula? thanks

[burakkaragoz · Accepted solution]

Hi @DHorsley1 ,

Thanks for clarifying! If you want the filtering to be dynamic and based on slicer selection (so that you don’t have to manually change the person in the DAX), you can use the SELECTEDVALUE function in your measures. Here’s how you can adapt the measures for dynamic selection:

dax

AdjBook_Table1 =
CALCULATE(
    SUM('YourTable'[adj. book of business]),
    'YourTable'[Billing Partner] = SELECTEDVALUE('YourSlicerTable'[Person]),
    'YourTable'[MMA] <> SELECTEDVALUE('YourSlicerTable'[Person])
)

AdjBook_Table2 =
CALCULATE(
    SUM('YourTable'[adj. book of business]),
    'YourTable'[MMA] = SELECTEDVALUE('YourSlicerTable'[Person]),
    'YourTable'[Billing Partner] <> SELECTEDVALUE('YourSlicerTable'[Person])
)

CombinedAdjBook =
[AdjBook_Table1] + [AdjBook_Table2]

• Replace 'YourSlicerTable'[Person] with the actual table and column you are using for the slicer.
• Place your slicer visual on the report page, so the user can select the person, and the card visual with [CombinedAdjBook] will update automatically based on the slicer selection.

If you have a more complex scenario or run into issues with slicer setup, just let me know your table/column names and I can help tailor the DAX further.

[v-veshwara-msft]

Hi @DHorsley1 ,

We’re following up regarding your query. If it has been resolved, please mark the helpful reply as the Accepted Solution to assist others facing similar challenges.

If you still need assistance, please share some sample data (without any sensitive information) so we can assist you more accurately.
Thank you.

[v-veshwara-msft]

Hi @DHorsley1 ,
Could you please let us know if your query has been resolved and if the response provided was helpful? If you're still facing issues, please share some sample data (without any sensitive information) so we can assist you more accurately. Feel free to reach out if you need any further assistance.

Thank you.

[v-veshwara-msft · Accepted solution]

Hi @DHorsley1 ,

Thanks for posting in Microsoft Fabric Community.

To combine the 'adj. book of business' values from the two filtered visuals into a single card, creating a separate measure that replicates your filter logic is the right approach, especially since both visuals are filtered independently and not through a relationship.

Based on the ongoing discussion, your setup involves filtering:

One visual where Billing Partner = selected person and MMA ≠ selected person

Another where MMA = selected person and Billing Partner ≠ selected person

Since the person is selected manually using a slicer, the measure can use SELECTEDVALUE to capture that selection and apply the above conditions inside CALCULATE.

To help write the exact DAX, please share some sample data without sensitive info, including the relevant columns.

Please let us know if the earlier suggestion from @burakkaragoz worked for you or if you need further assistance.

Hope this helps. Please reach out for further assistance.

Please consider marking the helpful reply as Accepted Solution and giving kudos to assist others with similar issues.

Thnak you.
Also thanks to @burakkaragoz for your continued guidance on this thread

[v-veshwara-msft]

Hi @DHorsley1 ,

Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries. 

Otherwise, feel free to reach out for further assistance.

Thank you.

[DHorsley1]

also the table is a visual table not a dataset, so I am receiving the error "Cannot find table 'BP not MMA"

[danextian]

It would be easier for anyeone to come up with a solution if you provided a sample data that is not an image and be readily copy pasted to Excel and from that sample data your expected result. It can take time to come up with a sample data that actually represents the actual but so is coming up with solutions.

[burakkaragoz]

Hi @DHorsley1 ,

If you want to sum the values from both visuals (with slicers applied, but no relationship), you’ll need a measure that ignores slicer context, or you need to “virtually” combine the tables.

Try this DAX measure to sum across all data, regardless of current filters:

dax

Total Adj Book of Business = CALCULATE(
    SUM('YourTable'[adj book of business]),
    ALL('YourTable')
)

If you want to sum both filtered results (with slicers), you need to make sure both sets of filters are reflected in your measure. If they use different slicers (and you’ve edited interactions), you’ll need to create a measure for each, then add them:

dax

FilteredSum1 = CALCULATE(SUM('YourTable'[adj book of business]), [Your Filter 1 Logic])
FilteredSum2 = CALCULATE(SUM('YourTable'[adj book of business]), [Your Filter 2 Logic])

TotalFilteredSum = [FilteredSum1] + [FilteredSum2]

Replace [Your Filter 1 Logic] and [Your Filter 2 Logic] with the actual filter expressions you use for each visual.

If you give more detail on your slicers/filters, I can write the exact DAX for you!

Let me know if this helps.

[DHorsley1]

Hi Burak,

Thanks for your help.

I have redacted the data for confidentiality issues but please see the visual.

Thanks,

Darren

[burakkaragoz]

@DHorsley1 ,

Thanks for sharing the visual, that actually makes things clearer. I see you’ve blurred out the sensitive info, all good.

Looking at your visuals, the main thing is that each one is using its own slicers/filters, so the logic I shared before still applies. If you want to sum up the numbers from both visuals, you need to make sure the DAX measure ignores the visual-level filters, or you calculate each separately with their own logic and then combine.

If you can let me know what filters or slicers are used on each visual (just describe them, no need for exact names), I can write the exact DAX formula for you. It’s just about matching the filter logic in each CALCULATE statement. Sometimes, if the visuals are set up with totally different filters, you’ll need to do a custom calculation for each.

Let me know a bit more detail and I’ll put together a ready-to-use DAX for your setup.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[DHorsley1]

Hi Burak,

In the first table, I filter for a name in Billing Partner and everyone except for that name in MMA. Then in the second table, I filter for a name in MMA and then everyone except for that name in billing partner. I then want to combined sum of 'adj. book of business' in a card visual. I really appreciate the support.

Thanks,
Darren

[burakkaragoz]

@DHorsley1 ,

Based on your setup:

• In the first table, you filter for a specific name in Billing Partner and everyone except that name in MMA.
• In the second table, you filter for a specific name in MMA and everyone except that name in Billing Partner.
• You want to combine the sum of 'adj. book of business' from both setups into a single card visual.

You can achieve this with two separate measures for each scenario, then sum them up in a final measure. Here’s an example DAX you can adapt:

dax

AdjBook_Table1 =
CALCULATE(
    SUM('YourTable'[adj. book of business]),
    'YourTable'[Billing Partner] = "TargetName",
    'YourTable'[MMA] <> "TargetName"
)

AdjBook_Table2 =
CALCULATE(
    SUM('YourTable'[adj. book of business]),
    'YourTable'[MMA] = "TargetName",
    'YourTable'[Billing Partner] <> "TargetName"
)

CombinedAdjBook =
[AdjBook_Table1] + [AdjBook_Table2]

• Replace "TargetName" with the actual name you are filtering for.
• Change 'YourTable' and [adj. book of business] to match your actual table and column names.

Then, use the [CombinedAdjBook] measure in your card visual to get the combined sum.

Let me know if you need further tweaks or if your filtering logic is more dynamic (for example, based on slicer selection). I can help adjust the DAX accordingly.

[DHorsley1]

Hi Burak,

It would be based on the slicer selection. I have to manually change the person im filtering for each time.

Thanks

[burakkaragoz · Accepted solution]

Hi @DHorsley1 ,

Thanks for clarifying! If you want the filtering to be dynamic and based on slicer selection (so that you don’t have to manually change the person in the DAX), you can use the SELECTEDVALUE function in your measures. Here’s how you can adapt the measures for dynamic selection:

dax

AdjBook_Table1 =
CALCULATE(
    SUM('YourTable'[adj. book of business]),
    'YourTable'[Billing Partner] = SELECTEDVALUE('YourSlicerTable'[Person]),
    'YourTable'[MMA] <> SELECTEDVALUE('YourSlicerTable'[Person])
)

AdjBook_Table2 =
CALCULATE(
    SUM('YourTable'[adj. book of business]),
    'YourTable'[MMA] = SELECTEDVALUE('YourSlicerTable'[Person]),
    'YourTable'[Billing Partner] <> SELECTEDVALUE('YourSlicerTable'[Person])
)

CombinedAdjBook =
[AdjBook_Table1] + [AdjBook_Table2]

• Replace 'YourSlicerTable'[Person] with the actual table and column you are using for the slicer.
• Place your slicer visual on the report page, so the user can select the person, and the card visual with [CombinedAdjBook] will update automatically based on the slicer selection.

If you have a more complex scenario or run into issues with slicer setup, just let me know your table/column names and I can help tailor the DAX further.