Forum Discussion

amsrivastavaa's avatar
amsrivastavaa
Helper III
3 years ago
Solved

Reference Table not changing

Hi Guys!!,   In Power BI Query Editor, I have created Table-A and then created Table-B as a reference table of Table-A and then apply Group by based on some column for one measure.   Now, on repo...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    I don't think that is possible because a calculated column formula does not respond to a change in slicers/filters - only measures do.

amsrivastavaa's avatar
amsrivastavaa
Helper III
3 years ago

HI Ashish_Mathur ,

 

Filters : Project

Table-A : Columns (Project, Type,Year,Amount)

 

ProjectTypeYearAmountTotal_Amount
P-1Credit20171060
P-2Credit20172060
P-3Credit20173060
P-1Credit2018100600
P-2Credit2018200600
P-3Credit2018300600

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 

 

ProjectTypeYearAmountTotal_Amount
P-2Credit20172060
P-3Credit20173060
P-2Credit2018200600
P-3Credit2018300600

 

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

 

ProjectTypeYearAmountTotal_Amount
P-2Credit20172050
P-3Credit20173050
P-2Credit2018200500
P-3Credit2018300500

 

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 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

These measures work.

Amt = SUM(Data[Amount])
Measure = CALCULATE([Amt],ALLSELECTED(Data[Project]))

Hope this helps.

  • amsrivastavaa's avatar
    amsrivastavaa
    Helper III
    3 years ago

    Hi Ashish_Mathur ,

     

    I want to add this column at DATA page, as I want to use this data some where else.

     

     

    1. While using provided DAX, its not giving me summation based on what Project left, instead it giving me individual amount of Measure.

     

    FIrst, I need to implement here at Data page.

    Secondly, when user select let say P-2 and P-3 then summation would be of AMOUNT for P-2 and P-3 only.

     

    Please suggest 

     

    Thanks

    Amit 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      3 years ago

      I don't think that is possible because a calculated column formula does not respond to a change in slicers/filters - only measures do.