Forum Discussion
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 '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
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:
daxAdjBook_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 AIHi 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
13 Replies
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:
daxTotal 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:
daxFilteredSum1 = 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.
Hi Burak,
Thanks for your help.
I have redacted the data for confidentiality issues but please see the visual.
Thanks,
Darren
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.
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.
also the table is a visual table not a dataset, so I am receiving the error "Cannot find table 'BP not MMA"
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 threadHi 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.
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.
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.
