Calculate Running Count of Coached Employees By Category and Date

[Anonymous]

Hello All,

Below is the dataset that i am using.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdBLDkAwFIXhvdyxBLfdAPWW2EBjIEiM2P+MiaTEOWaNL43z13vJJRJ3TPO2LtcpjdNYE01kjLw4TCWmClONqcHUYuowZdeH4diBFlT737shKW5X3K64Qp9LAH0EBppTdVRLqg3Vluq71OBSQ1sMbTG0hfzUPp8X0MceS/dYuufW8QQ=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeName_ID = _t, Coached_NonCoached = _t, Date = _t])
in
    Source

im trying to calculate the running count of employees who are coached.

As in above dataset, i have four months of data of employee's getting coached and non coached for each month and the total count of emp is 11.

In January, if a employee is Coached, he can be non coached in next month.

And if a employee is non coached in january, he can be coached in next coming any of the months.

Once a employee is coached, he will always be considered as coached.

So the output that i am expexting is as below.

Any help or suggestions that how can i get this.

Thanks,

Mohan V.

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can try to use the following measure formula to achieve your requirement:

Measure =
VAR currType =
    SELECTEDVALUE ( T1[Coached_NonCoached] )
VAR coached =
    CALCULATETABLE (
        VALUES ( T1[EmployeeName_ID] ),
        FILTER (
            ALLSELECTED ( T1 ),
            [Date] <= MAX ( T1[Date] )
                && [Coached_NonCoached] = "Coached"
        )
    )
VAR noncoached =
    CALCULATETABLE (
        VALUES ( T1[EmployeeName_ID] ),
        FILTER (
            ALLSELECTED ( T1 ),
            [Date] <= MAX ( T1[Date] )
                && [Coached_NonCoached] = "NonCoached"
        )
    )
RETURN
    SWITCH (
        currType,
        "Coached", CONCATENATEX ( coached, [EmployeeName_ID], "," ),
        "NonCoached", CONCATENATEX ( EXCEPT ( noncoached, coached ), [EmployeeName_ID], "," )
    )

Notice: power bi visuals will auto-hide blank rows, you can click on the category field and choose 'show item with not data' to force display these hide rows.
Regards,

Xiaoxin Sheng

[Portrek]

Hi Mohan.

You need to create two measures.

1º To count the Coached and NonCoached.

measure count coached =    COUNTROWS('table')

2º Concatenate the results

Measure Emps Include = CONCATENATEX('table'
,
'table'[EmployeeName_ID]
,
", "
)

After that, put the measures in the table visual.

Best Regards

[Anonymous]

Hi @Portrek  Thanks for the reply.

I guess my ask was misfired.

Employee's include is just for understanding purpose, which will give a idea how the count should be based on the coached employee's and what should be the value.

The Count column is what the output that i am expecting.

I would like to have running count of distinct Coached employees.

Please help.

Thanks,

Mohan V.