Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello guys,
I'm trying to build a performance KPI from the source table below.
I update it every week with a bunch of information and the "as of" column is the driver for the "WeekNum" column.
As of | Item | Responsible | Delivery Date | Late | WeekNum |
10.aug.20 | A | John | 01.aug.20 | 1 | 33 |
10.aug.20 | A | Cesar | 05.aug.20 | 1 | 33 |
10.aug.20 | B | Mark | 10.jan.21 | 0 | 33 |
10.aug.20 | E | Candace | 05.feb.21 | 0 | 33 |
10.aug.20 | T | Byron | 07.sep.20 | 0 | 33 |
10.aug.20 | E | Laura | 08.nov.21 | 0 | 33 |
10.aug.20 | E | Daniel | 15.feb.20 | 1 | 33 |
10.aug.20 | E | Daniel | 05.jun.20 | 1 | 33 |
10.aug.20 | E | Daniel | 08.jun.20 | 1 | 33 |
10.aug.20 | E | Candace | 8.mar.20 | 1 | 33 |
10.aug.20 | E | Byron | 9.apr.21 | 0 | 33 |
10.aug.20 | E | Byron | 10.may.21 | 0 | 33 |
10.aug.20 | E | Mark | 18.may.20 | 1 | 33 |
17.aug.20 | E | Laura | 07.sep.20 | 0 | 34 |
17.aug.20 | E | Candace | 08.nov.21 | 0 | 34 |
17.aug.20 | E | Laura | 15.feb.20 | 1 | 34 |
17.aug.20 | E | Cesar | 05.jun.20 | 1 | 34 |
17.aug.20 | E | Mark | 08.jun.20 | 1 | 34 |
17.aug.20 | E | John | 8.mar.20 | 1 | 34 |
17.aug.20 | E | Cesar | 9.apr.21 | 0 | 34 |
17.aug.20 | E | Mark | 10.may.21 | 0 | 34 |
17.aug.20 | E | Candace | 10.jan.21 | 0 | 34 |
17.aug.20 | E | Mark | 07.sep.20 | 0 | 34 |
17.aug.20 | E | Cesar | 08.nov.21 | 0 | 34 |
To do so, i'm using CALCULATE + COUNTA and CALCULATE + SUM... but it's not working!
My expectation was to get a result table like this (to be added to the report page 😁😞
WeekNum | Total items | Late items | Performance |
33 | 13 | 7 | 46% |
John | 1 | 1 | 0% |
Cesar | 1 | 1 | 0% |
Mark | 2 | 1 | 50% |
Candace | 2 | 1 | 50% |
Byron | 3 | 0 | 100% |
Laura | 1 | 0 | 100% |
Daniel | 3 | 3 | 0% |
34 | 11 | 4 | 64% |
John | 1 | 1 | 0% |
Cesar | 3 | 1 | 67% |
Mark | 3 | 1 | 67% |
Candace | 2 | 0 | 100% |
Byron | 0 | 0 | n/a |
Laura | 2 | 1 | 50% |
Daniel | 0 | 0 | n/a |
Total items : COUNTIF: number of items by name and week number
Late items : SUMIF: number of late items by name and week number
Performance : 1-(Late items / Total items)
Can you pleeeease help me with those DAX formulas? I'm a newbie at it and i'm trying for more than 3 days now 😅
Really appreciate your support!!
Solved! Go to Solution.
You should be able to make a matrix visual with your Week column and Responsible column in the rows, with these 3 measures in the Values area (replace Table with your actual table name).
Total Items = COUNT(Table[Item])
Late Items = CALCULATE([Total Items], Table[Late] = 1)
Performance = DIVIDE([Late Items], [Total Items])
(format Performance as percentage).
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
You should be able to make a matrix visual with your Week column and Responsible column in the rows, with these 3 measures in the Values area (replace Table with your actual table name).
Total Items = COUNT(Table[Item])
Late Items = CALCULATE([Total Items], Table[Late] = 1)
Performance = DIVIDE([Late Items], [Total Items])
(format Performance as percentage).
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hey Pat!
Worked like a charm!! Thanks for your prompt assistance! really appreciated!
BR,
Edson
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |