Forum Discussion

DHorsley1's avatar
DHorsley1
New Member
1 year ago
Solved

Sum 2 filtered visuals (not using relationship)

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 ...
Need Help
  • burakkaragoz's avatar
    burakkaragoz
    1 year ago

    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.
    translation and formatting supported by AI

  • v-veshwara-msft's avatar
    v-veshwara-msft
    1 year ago

    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

burakkaragoz's avatar
burakkaragoz
Super User
1 year ago

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's avatar
DHorsley1
New Member
1 year ago

Hi Burak,

 

Thanks for your help.

 

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

 

Thanks,

Darren

  • burakkaragoz's avatar
    burakkaragoz
    Super User
    1 year ago

    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's avatar
      DHorsley1
      New Member
      1 year ago

      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's avatar
        burakkaragoz
        Super User
        1 year ago

        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.