Excluding a calculated column Explicitly from Column Totals

[Anonymous]

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 

1. When only Agency is selected, Agency values should appear normally
2. When multiple programs (including Agency) are selected, the total column should exclude Agency's values to avoid double-counting
3. The challenge is that Power BI automatically calculates totals at the system level, and I  need to override that behavior for the total column 

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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.

[lbendlin · Accepted solution]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Anonymous]

https://drive.google.com/file/d/1mFnNtz-ojkr7KuEI1W2ur8kHV_dmXfNC/view?usp=drive_link

[lbendlin]

Thank 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")

RETURN

IF(COUNTROWS(NoAgency)=0,CALCULATE(Actuals, PSC_Name="Agency"),CALCULATE(Actuals, PSC_Name in NoAgency))

[Anonymous]

updated sample  I will try applying logic Thanks

[lbendlin]

link asks for access - please check

[Tan_g]

That solution. Didn't work either  because the total was still shown for all the column and since I want able to solve my contract ended  

thank you for help