How to create a measure to calculate count of active user % in specific department in given month

[ZhaoxiZ]

Hello, 

I am trying to get some help here for my dashboard. Thank you for anyone who will help in advance! 

we are trying to track the % of user on a plaftform for each department, month by month.  

The logic : 

sum of the active user of specific department of given month / sum of the active user  for the given month

the example will be for accounting investment department active user % for Nov 2022 will be 50/399.

Notes: 

1. 720 Cnt active user is a measure I created. ( logic: calculate( distinctcount(memberID), Logon='1')

2. highlighted 50 is also from cnt active user column, broken down by department. 

3. my goal is to create a line chart to show the % active user of each different department month by month

[v-shex-msft]

Hi @ZhaoxiZ,

You can try to use the following measure formula on the table visual with year month as category to get correspond percentage:

formual =
DIVIDE (
    CALCULATE (
        COUNT ( Table[department] ),
        ALLSELECTED ( Table ),
        VALUES ( Table[Date] )
    ),
    CALCULATE ( [Cnt active users], ALLSELECTED ( Table ), VALUES ( Table[Date] ) )
)

Regards,

Xiaoxin Sheng

[ZhaoxiZ]

Hi Xiaoxin, 

Thank you for the quick response, I have tried the DAX you have provide above, somhow the values are not correct. This is what I have found. 

The denominator 

CALCULATE ( [Cnt active users], ALLSELECTED ( Table ), VALUES ( Table[Date] ) )

is correct, it returns all the active user count for the given month. 

The Numerator

CALCULATE (
        COUNT ( Table[department] ),
        ALLSELECTED ( Table ),
        VALUES ( Table[Date] )
    )

seems counting the total number of the department of the given month, it returns large numbers as below:

For 'performance' department,  2023- Feb. The 'Cnt active users' =18. 

The value returned should be 18/ 412=4.37%

Three values we need for the calculation are 'Date', 'Cnt active user', 'department' becasue it shows the right numbe when I put these three columns in the view. I just dont know what synatx should I use to return the right value. Thank you for your help in advance! 

[v-shex-msft]

Hi @ZhaoxiZ,

Can you please share some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[ZhaoxiZ]

Hi Xiaoxin, 

Thank you! Please find the dummy data as attached below. 

ID	Date	Active_Flag	Department
1	2023-01-01	0	Performance
2	2023-01-02	0	Investment Accounting
3	2023-01-03	0	Investment Accounting
4	2023-01-04	0	Investment Accounting
5	2023-01-05	1	Investment Accounting
6	2023-01-06	1	Investment Accounting
7	2023-01-07	1	Investment Accounting
8	2023-01-08	1	Performance
9	2023-01-09	1	Performance
10	2023-01-10	0	Performance
11	2023-01-11	1	Performance
12	2023-01-12	1	Performance
13	2023-01-13	1	Performance
14	2023-02-14	1	Performance
15	2023-02-15	1	Investment Accounting
16	2023-02-16	1	Investment Accounting
17	2023-02-17	1	Investment Accounting
18	2023-02-18	0	Investment Accounting
19	2023-02-19	0	Investment Accounting
20	2023-02-20	0	Performance
21	2023-02-21	0	Performance
22	2023-02-22	1	Performance
23	2023-02-23	1	Performance
24	2023-03-24	1	Performance
25	2023-03-25	1	Performance
26	2023-03-26	1	Performance
27	2023-03-27	1	Performance
28	2023-03-28	1	Performance
29	2023-03-29	1	Investment Accounting
30	2023-03-30	1	Investment Accounting
31	2023-03-31	1	Investment Accounting

1. count: distinct count of active flag=1

2. logic: numerator =5 (count of active user for performance of given month)

              denominator= 8 ( total count of the active user for the given month)