Forum Discussion
Reference Table not changing
I don't think that is possible because a calculated column formula does not respond to a change in slicers/filters - only measures do.
HI Ashish_Mathur ,
Filters : Project
Table-A : Columns (Project, Type,Year,Amount)
| Project | Type | Year | Amount | Total_Amount |
| P-1 | Credit | 2017 | 10 | 60 |
| P-2 | Credit | 2017 | 20 | 60 |
| P-3 | Credit | 2017 | 30 | 60 |
| P-1 | Credit | 2018 | 100 | 600 |
| P-2 | Credit | 2018 | 200 | 600 |
| P-3 | Credit | 2018 | 300 | 600 |
I want to include another column say Total_Amount which can hold summataion of column amount based on Type and Year, in above case it will be (10+20+30=60) for Type=Credit and Year =2017 And it will be (100+200+300=600) for Type=Credit and Year=2017.
To calculate, Total_Amount, I have used below DAX in Power BI DATA page
TotalAmount = CALCULATE(SUM('Transaction'[Amount]),ALLEXCEPT('Transaction','Transaction'[Type],'Transaction'[Year]))
As this TotalAmount column is created at DATA Page, so I am able to filter the data based on TotalAmount on Report Canvas, till here things are fine.
Now problem comes when I have UN-selected P-1 in Project filter in Report canvas, then I will have data like this as shown below
| Project | Type | Year | Amount | Total_Amount |
| P-2 | Credit | 2017 | 20 | 60 |
| P-3 | Credit | 2017 | 30 | 60 |
| P-2 | Credit | 2018 | 200 | 600 |
| P-3 | Credit | 2018 | 300 | 600 |
i.e. P-1 data is not available now, that perfect.
And, in this case my TotalAmount which I have calculated via DAX at Data page is still showing Total that includes P-1 data as well, which is not correct.
I want, TotalAmount will always be calculated based on data avaialble after filterationas shown below
| Project | Type | Year | Amount | Total_Amount |
| P-2 | Credit | 2017 | 20 | 50 |
| P-3 | Credit | 2017 | 30 | 50 |
| P-2 | Credit | 2018 | 200 | 500 |
| P-3 | Credit | 2018 | 300 | 500 |
I want, this too be done at table level itsels so that I can use this table further as well, if i will use any measure filteration concpets at Report Canvas level, I will not be able to use that data any further.
Please suggest!!''
Thanks
Amit
Hi, amsrivastavaa
You can try the following methods.
Measure:
Total_Amount = CALCULATE(SUM('Transaction'[Amount]),ALLSELECTED('Transaction'[Project]))
Data view:
The calculated columns of the Data view are fixed. Cannot be changed by slicers or filters.
This is by design. Power BI is now unable to achieve what you need . You can submit an idea for it at https://ideas.powerbi.com/ideas/ and wait for users with the same needs as you to vote for you to help make it happen as soon as possible.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.