Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all
YEAR | MINOR GROUP | PRODUCT | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC | JAN | FEB | MAR | TOTAL QTY | TOTALVAL | month |
20212022 | ASMSP | 425PS | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 5570 | APR |
20212022 | MINI | 3SH | 12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 12 | 35205 | APR |
20212022 | MINI | 3SH | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 15648 | APR |
20212022 | MINI | 4SH | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 16549 | APR |
20212022 | MINI | 4SH | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 62442 | APR |
20212022 | MINI | 5SH | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 21715 | APR |
20212022 | CBC | BCC | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 22076 | APR |
20212022 | CBC | BCC | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 50267 | APR |
20212022 | CBC | BCC | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 25980 | APR |
20212022 | ASMSP | 425PS | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 5570 | DEC |
20212022 | MINI | 3SH | 12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 12 | 35205 | DEC |
20212022 | MINI | 3SH | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 15648 | DEC |
20212022 | MINI | 4SH | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 16549 | DEC |
20212022 | MINI | 4SH | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 62442 | DEC |
20212022 | MINI | 5SH | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 21715 | DEC |
20212022 | CBC | BCC | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 22076 | DEC |
20212022 | CBC | BCC | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 50267 | DEC |
20212022 | CBC | BCC | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 25980 | DEC |
20222023 | ASMSP | 425PS | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 5570 | APR |
20222023 | MINI | 3SH | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 35205 | APR |
20222023 | MINI | 3SH | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 15648 | APR |
20222023 | MINI | 4SH | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 16549 | APR |
20222023 | MINI | 4SH | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 62442 | APR |
20222023 | MINI | 5SH | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6 | 21715 | APR |
20222023 | CBC | BCC | 7 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 7 | 22076 | APR |
20222023 | CBC | BCC | 8 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 8 | 50267 | APR |
20222023 | CBC | BCC | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 9 | 25980 | APR |
20222023 | ASMSP | 425PS | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 5570 | DEC |
20222023 | MINI | 3SH | 11 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 11 | 35205 | DEC |
20222023 | MINI | 3SH | 12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 12 | 15648 | DEC |
20222023 | MINI | 4SH | 13 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 13 | 16549 | DEC |
20222023 | MINI | 4SH | 14 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 14 | 62442 | DEC |
20222023 | MINI | 5SH | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 15 | 21715 | DEC |
20222023 | CBC | BCC | 16 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 16 | 22076 | DEC |
20222023 | CBC | BCC | 17 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 17 | 50267 | DEC |
20222023 | CBC | BCC | 18 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 18 | 25980 | DEC |
Required result, based on the conditions | |||||||||||||||||
Target for 20222023 = total qty of 20212022 + 80% for ASMSP | Target for 20222023 = total qty of 20212022 + 100% for CBC | Target for 20222023 = total qty of 20212022 + 150% for MINI | |||||||||||||||
I want to keep the above target for each of the Minor group and measure its performace for current year | |||||||||||||||||
MINOR | 20212022 | Increase Target | Qty | Current year 20222023 | |||||||||||||
ASMSP | 2 | 80% | 3.6 | 11 | |||||||||||||
CBC | 8 | 100% | 16 | 75 | |||||||||||||
MINI | 36 | 150% | 90 | 85 |
Thanks in advance ...
Regards
SRK
Hi @srkase,
I'd like to suggest you use unpivot columns feature to convert these month fields to attribute and value, then you can simply use measure expression to calculate and compare results based on current year and month values.
Unpivot columns (Power Query) (microsoft.com)
Regards,
Xiaoxin Sheng
Sorry,,, There is a last column in my data representing the month ... probably at the time of copying it might have been left out
here is my data
YEAR | MINOR GROUP | PRODUCT | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC | JAN | FEB | MAR | TOTAL QTY | TOTALVAL | month |
20212022 | ASMSP | 425PS | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 5570 | APR |
20212022 | MINI | 3SH | 12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 12 | 35205 | APR |
20212022 | MINI | 3SH | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 15648 | APR |
20212022 | MINI | 4SH | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 16549 | APR |
20212022 | MINI | 4SH | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 62442 | APR |
20212022 | MINI | 5SH | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 21715 | APR |
20212022 | CBC | BCC | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 22076 | APR |
20212022 | CBC | BCC | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 50267 | APR |
20212022 | CBC | BCC | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 25980 | APR |
20212022 | ASMSP | 425PS | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 5570 | DEC |
20212022 | MINI | 3SH | 12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 12 | 35205 | DEC |
20212022 | MINI | 3SH | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 15648 | DEC |
20212022 | MINI | 4SH | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 16549 | DEC |
20212022 | MINI | 4SH | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 62442 | DEC |
20212022 | MINI | 5SH | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 21715 | DEC |
20212022 | CBC | BCC | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 22076 | DEC |
20212022 | CBC | BCC | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 50267 | DEC |
20212022 | CBC | BCC | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 25980 | DEC |
20222023 | ASMSP | 425PS | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 5570 | APR |
20222023 | MINI | 3SH | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 35205 | APR |
20222023 | MINI | 3SH | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 15648 | APR |
20222023 | MINI | 4SH | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 16549 | APR |
20222023 | MINI | 4SH | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 62442 | APR |
20222023 | MINI | 5SH | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6 | 21715 | APR |
20222023 | CBC | BCC | 7 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 7 | 22076 | APR |
20222023 | CBC | BCC | 8 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 8 | 50267 | APR |
20222023 | CBC | BCC | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 9 | 25980 | APR |
20222023 | ASMSP | 425PS | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 5570 | DEC |
20222023 | MINI | 3SH | 11 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 11 | 35205 | DEC |
20222023 | MINI | 3SH | 12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 12 | 15648 | DEC |
20222023 | MINI | 4SH | 13 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 13 | 16549 | DEC |
20222023 | MINI | 4SH | 14 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 14 | 62442 | DEC |
20222023 | MINI | 5SH | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 15 | 21715 | DEC |
20222023 | CBC | BCC | 16 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 16 | 22076 | DEC |
20222023 | CBC | BCC | 17 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 17 | 50267 | DEC |
20222023 | CBC | BCC | 18 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 18 | 25980 | DEC |
User | Count |
---|---|
104 | |
86 | |
79 | |
70 | |
70 |
User | Count |
---|---|
112 | |
100 | |
98 | |
72 | |
66 |