CODEWITHSUNDEEP
dax
Mahendran C
Mahendran C

Reputation: 3

how to sum day wise unique count to MTD

I want to calculate tasks per day(no of tasks/no of unique users) in my dashboard.

I used the distinct count to get the unique no of employees. When I put the measure in pivot, day wise calculation is okay but at the end in MTD it is not added the day wise unique no of emp.

Tasks per day:=divide(sum(fdata[resolved]),distinctcount(fdata[sap id]))

I expect the output like second table.

Model 

Date       ID   Resolved        
12-05-2019  a   1           
12-05-2019  a   1           
12-05-2019  b   1           
13-05-2019  a   1                       
13-05-2019  b   1                       
13-05-2019  c   1

Expected Result 

Date       No of emp    Resolved    Task Per Day    
12-05-2019    2            3             1.5    
13-05-2019    3            3             1  
Grand Total   **5**            6             1.2

Upvotes: 0

Views: 109

Answers (1)

Hernan Demczuk
Hernan Demczuk

Reputation: 405

This will do the trick:

Tasks per day:= 
    IF(
        ISFILTERED( fdata[date] )
        , DISTINCTCOUNT( fdata[sap id] )
        , COUNTROWS( 
            GROUPBY( 
                fdata
                , fdata[date]
                , fdata[sap id] 
            )
        )
     )

Upvotes: 1

Related Questions