Forum Discussion
Excluding a calculated column Explicitly from Column Totals
Sample file I have a Matrix table
The source has multiple Programs and a calculated column"Agency" thats has total of alll other programs. My problem is Power BI Total Column shows Agency total when I select multiple programs. I would like
- When only Agency is selected, Agency values should appear normally
- When multiple programs (including Agency) are selected, the total column should exclude Agency's values to avoid double-counting
- The challenge is that Power BI automatically calculates totals at the system level, and I need to override that behavior for the total column
That should be possible.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
9 Replies
That should be possible.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Thank you for the sample file. It is slightly too big to attach it back here. Try creating a smaller version.
You have a lot of measures that are filtering the "All Programs" table. Yet that table is disconnected so these filters will have no effect on the data model. What you would need to do instead is to project the slicer choices onto the fact table via TREATAS - for that you would need to explain how PSC_Name maps into Actuals.
After that the formula would go something like this
Corrected Total =VAR SelectedPrograms = VALUES(All_Programs[PSC_NAME])VAR NoAgency = FILTER(SelectedPrograms,[PSC_Name]<>"Agency")RETURNIF(COUNTROWS(NoAgency)=0,CALCULATE(Actuals, PSC_Name="Agency"),CALCULATE(Actuals, PSC_Name in NoAgency))
Hi Anonymous,
Thank you for reaching out in Microsoft Community Forum.
Thank you lbendlin for the helpful response.
As suggested solution by Ibendlin, provided link requires the approval. Please provide the sample file with access.
Please continue using the Microsoft community forum.
Regards,
Pavan.Hi Anonymous,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept as Solution" and give a 'Kudos' so other members can easily find it.
Thank you,
Pavan.Hi Anonymous,
I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.
Please continue using Microsoft community forum.
Thank you,
Pavan.