Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have line level data of production and productivity %. I want to aggregate the productivity % at each level of selection. I am currently calculating manually at an employee MTD level using custom columns. I want to calculate this using DAX, any ideas?
Date | Employee ID | EmployeeName | Reporting Authority | Shift | Location | Production # | Target | Productivity % |
2-Mar-21 | 18 | C | H | Night | Australia | 72 | 75 | 96% |
5-Mar-21 | 19 | C | H | Night | Australia | 61 | 75 | 81% |
9-Mar-21 | 5 | A | F | Day | USA | 52 | 75 | 69% |
9-Mar-21 | 21 | D | I | Night | Australia | 56 | 75 | 75% |
11-Mar-21 | 4 | A | F | Day | USA | 94 | 75 | 125% |
14-Mar-21 | 15 | C | H | Night | London | 64 | 75 | 85% |
27-Mar-21 | 20 | D | I | Night | Australia | 61 | 75 | 81% |
28-Mar-21 | 8 | B | G | Day | London | 71 | 75 | 95% |
5-Apr-21 | 24 | D | I | Night | Australia | 50 | 75 | 67% |
8-Apr-21 | 1 | A | F | Day | USA | 85 | 75 | 113% |
11-Apr-21 | 10 | B | G | Day | London | 51 | 75 | 68% |
16-May-21 | 25 | D | I | Night | Australia | 53 | 75 | 71% |
2-Jun-21 | 3 | A | F | Day | USA | 80 | 75 | 107% |
5-Jun-21 | 17 | C | H | Night | London | 83 | 75 | 111% |
17-Jun-21 | 9 | B | G | Day | London | 84 | 75 | 112% |
19-Jun-21 | 23 | D | I | Night | Australia | 63 | 75 | 84% |
24-Jun-21 | 7 | B | G | Day | USA | 58 | 75 | 77% |
28-Jul-21 | 14 | C | H | Night | London | 51 | 75 | 68% |
3-Aug-21 | 6 | A | F | Day | USA | 79 | 75 | 105% |
18-Aug-21 | 12 | B | G | Day | London | 100 | 75 | 133% |
31-Aug-21 | 16 | C | H | Night | London | 63 | 75 | 84% |
3-Oct-21 | 11 | B | G | Day | London | 64 | 75 | 85% |
14-Oct-21 | 22 | D | I | Night | Australia | 86 | 75 | 115% |
24-Oct-21 | 26 | D | I | Night | Australia | 60 | 75 | 80% |
28-Oct-21 | 13 | C | H | Night | London | 60 | 75 | 80% |
29-Oct-21 | 2 | A | F | Day | USA | 88 | 75 | 117% |
The visual I am currently using is a donut chart where I aggregate users based on productivity level. 0-90%, 91-95%,96%-100% and >100%. I am using MTD aggregation using custom columns and I'm able to acheive this. I'm trying to do the same calculation dynamically. If I choose my location as australia, I want the number of users to reflect only for australia, if I make any changes to time period I want it to calculate only for that period.
I want to apologize that I did not include in the original post.
Try creating a new Groups table to use for your groups.
Then you can define a measure to bucket the table rows into these groups:
GroupCount =
VAR Low = SELECTEDVALUE ( Groups[Low] )
VAR High = SELECTEDVALUE ( Groups[High] )
RETURN
COUNTROWS (
FILTER (
Prod,
Prod[Productivity %] >= Low
&& ( Prod[Productivity %] < High || ISBLANK ( High ) )
)
)
Sorry for the delayed response. In my case count rows does not work as I want distinct count of Employee Name. Since Filter evaluates at a row level, the same employee is part of multiple bins. Any way to counter this?
Try this:
GroupCount =
VAR Low = SELECTEDVALUE ( Groups[Low] )
VAR High = SELECTEDVALUE ( Groups[High] )
RETURN
CALCULATE (
DISTINCTCOUNT ( Prod[Employee Name] ),
FILTER (
Prod,
Prod[Productivity %] >= Low
&& ( Prod[Productivity %] < High || ISBLANK ( High ) )
)
)
I tried this one and it does not work. Just give me a couple of days, and I will create a pbix file with the problem and desired solution. My explanation using text is very poor. Thanks for the continued support.
Can you specify what the end result should look like?
Hi Alexis Olson,
I have attached my requirement above.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
104 | |
103 | |
87 | |
61 |
User | Count |
---|---|
168 | |
138 | |
134 | |
102 | |
86 |