Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ZhaoxiZ
Frequent Visitor

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

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.  

ZhaoxiZ_1-1679683378411.png

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

 

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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:

 

ZhaoxiZ_1-1679938710926.png

 

 

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! 

 

 

ZhaoxiZ_2-1679939582163.png

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin, 

 

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

IDDate  Active_FlagDepartment
12023-01-010Performance
22023-01-020Investment Accounting 
32023-01-030Investment Accounting 
42023-01-040Investment Accounting 
52023-01-051Investment Accounting 
62023-01-061Investment Accounting 
72023-01-071Investment Accounting 
82023-01-081Performance
92023-01-091Performance
102023-01-100Performance
112023-01-111Performance
122023-01-121Performance
132023-01-131Performance
142023-02-141Performance
152023-02-151Investment Accounting 
162023-02-161Investment Accounting 
172023-02-171Investment Accounting 
182023-02-180Investment Accounting 
192023-02-190Investment Accounting 
202023-02-200Performance
212023-02-210Performance
222023-02-221Performance
232023-02-231Performance
242023-03-241Performance
252023-03-251Performance
262023-03-261Performance
272023-03-271Performance
282023-03-281Performance
292023-03-291Investment Accounting 
302023-03-301Investment Accounting 
312023-03-311Investment 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) 

ZhaoxiZ_0-1680024565763.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors