PowerBI Dax measure values as rows in a table

Question

I have a really confusing doubt in PowerBI.

I have a sample data as attached and slicers for Emp Code and Date.

firstly, I need to bucket the attendance of each employee. i.e., "below 2 " or "above 2" which is dynamic with the slicers. For this I have used the below measure which works fine.

Bucket =
var CountPerDate = CALCULATE(COUNTROWS('Table'), ALLSELECTED('Table'[Date]))
var b = IF (CountPerDate > 2, "Above 2","Below 2")
RETURN b

Now i need to create another table based on this bucket as Attached. Both the bucket and % must be dynamic as it needed to change according to the slicers (Date&Emp code). Can you guys please help me find a solution for the bucket & % table?

Answer 1

You will need to create a new table for the bucket.

| Bucket  |
| ------- |
| Above 2 |
| Below 2 |

Either add it view PowerQuery or via DAX as a Calculated Table with:

Dim Bucket = DATATABLE("Bucket", STRING, { { "Above 2" }, { "Below 2" } })

Then you can create two Measures with the following:

Bucket Emp count = 
  IF(
    ISINSCOPE('Dim Bucket'[Bucket]),
    COUNTROWS( FILTER(VALUES('Table'[Emp Code]), [Bucket] = SELECTEDVALUE('Dim Bucket'[Bucket])) ),
    COUNTROWS( VALUES('Table'[Emp Code]) )
  )

Bucket Emp % = 
  DIVIDE(
    [Bucket Emp count],
    [Bucket Emp count]( REMOVEFILTERS('Dim Bucket'[Bucket]) )
  )

Then using the new table above (Dim Bucket), you can create a Table visual like this as an example: