Forum Discussion

AndyDD_UK's avatar
AndyDD_UK
Icon for Helper III rankHelper III
3 years ago

Help with DAX 2

In my Power BI report I have a page for 'sales by employee'.   Management want to select a period of weeks/month and see if any employees have not made any sales in a week (or more).   I have a m...
BeaBF's avatar
BeaBF
Icon for Super User rankSuper User
3 years ago

AndyDD_UK Hi! o create a Pass/Fail tag for employees who have missed a week of sales, you can create a measure that checks whether an employee has made a sale in all the selected weeks for the period:

 

Pass/Fail =
VAR SelectedWeeks = SELECTEDVALUES('Date'[Week])
VAR SalesWeeks = CALCULATETABLE(
VALUES('Date'[Week]),
FILTER('Sales', 'Sales'[Sales] > 0)
)
RETURN
IF(
COUNTROWS(SalesWeeks) = COUNTROWS(SelectedWeeks),
"Pass",
"Fail"
)

 

You can add this measure to your matrix as the first column (Column A) and the Employee ID column as the second column (Column B). For the rows, you can use the week or month column from your date table. Finally, for the values, use the Sales column from your Sales table.

The resulting matrix will show the Pass/Fail tag for each employee in each week/month for the selected period. If an employee missed a week of sales, they will have a Fail tag for that week/month, and if they made a sale in all selected weeks, they will have a Pass tag for the period.

 

BBF