Forum Discussion

MandyL's avatar
MandyL
Icon for Helper I rankHelper I
3 years ago

How to include the filter out data on the DAX Calculate function

Hi Team,

There are three pages on the PBI report, each page showing the project status.
If we filtered the Project_Status=”Win” on the first page, may I know how to change the calculate column (Win%)? As the Sales_Closed and Sales_Pending value are showing blank on the first page. Is there any way to show the filter out data? If we don’t consider the “Edit Interactions” function. Thanks!



Calculate column:

Win%=
VAR Sales_WIN= CALCULATE( ‘Project’[Sales Amount],  Filter (All (‘Project’), [Project_Status]=”Win”))
VAR Sales_Closed= CALCULATE( ‘Project’[Sales Amount],  Filter (All (‘Project’), [Project_Status]=”Closed”))
VAR Sales_Pending= CALCULATE( ‘Project’[Sales Amount],  Filter (All (‘Project’), [Project_Status]=”Pending”))

Return
Sales_WIN/(Sales_WIN+Sales_Closed+Sales_Pending)

1 Reply

  • Hi,

     

    You could try change ALL to ALLSELECTED 

     

    Win%=
    VAR Sales_WINCALCULATE( ‘Project’[Sales Amount],  Filter (ALLSELECTED (‘Project’), [Project_Status]=”Win”))
    VAR Sales_ClosedCALCULATE( ‘Project’[Sales Amount],  Filter (ALLSELECTED(‘Project’), [Project_Status]=”Closed”))
    VAR Sales_PendingCALCULATE( ‘Project’[Sales Amount],  Filter (ALLSELECTED(‘Project’), [Project_Status]=”Pending”))

    Return
    Sales_WIN/(Sales_WIN+Sales_Closed+Sales_Pending)