Forum Discussion

aausten22's avatar
aausten22
Helper I
3 years ago

DAX Calculation using ALL and SELECTEDVALUE

Hi There, 

 

I am trying to create a CALCULATE DAX formula where I am trying to calculate the whole column (without filters) - calculation of the same column with SELECTEDVALUE

So the outcome would be: Whole column - Column filtered by Slicer = XX Days

 

The formula us currently as follows

Per-staff variance from P&T average = CALCULATE(SUM('Data Set - Leave Liability'[Total Days]), All('Data Set - Leave Liability'[Branch]),'Data Set - Leave Liability'[Branch]="Policy & Trade") - CALCULATE(SUM('Data Set - Leave Liability'[Total Days]), 'Data Set - Leave Liability'[Directorate] = SELECTEDVALUE('Directorate List'[Directorate]))
 
This brings back 0 fo the amount which I'm guessing is something to do with the slicer having a value. Hopefully someone can help with this and that the end goal is clear.
Need Help
Tips and Tricks

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago
    CALCULATE(SUM('Data Set - Leave Liability'[Total Days]), All('Data Set - Leave Liability'[Branch]),'Data Set - Leave Liability'[Branch]="Policy & Trade")

    The ALL isn't really required here since you then override the filter context anyway.

     

    CALCULATE(SUM('Data Set - Leave Liability'[Total Days]), 'Data Set - Leave Liability'[Directorate] = SELECTEDVALUE('Directorate List'[Directorate]))

    This requires more information - like what the data model looks like, and what the measure definition is for [Total Days].

     

    Read about EVALUATEANDLOG - it is a great tool to validate your formula results.

    • aausten22's avatar
      aausten22
      Helper I
      3 years ago

      I am wanting to find the difference between the [Branch] against the [Directorate].

       

      The dataset is around leave days etc so needing a comparison between the two sets

       

      I have tried taking out the ALL though this is still getting the same result