Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have following Data-set
FISCAL YEAR MONTH SALE
FY 19-20 | JUL | 850 |
FY 19-20 | AUG | 120 |
FY 19-20 | SEP | 630 |
FY 19-20 | OCT | 9910 |
FY 19-20 | NOV | 4250 |
FY 19-20 | DEC | 320 |
FY 19-20 | JAN | 90 |
FY 19-20 | FEB | 120 |
FY 19-20 | MAR | 580 |
FY 19-20 | APR | 740 |
FY 19-20 | MAY | 690 |
FY 19-20 | JUN | 1140 |
FY 20-21 | JUL | 890 |
FY 20-21 | AUG | 5500 |
FY 20-21 | SEP | 360 |
FY 20-21 | OCT | 210 |
FY 20-21 | NOV | 25 |
FY 20-21 | DEC | 140 |
FY 20-21 | JAN | 780 |
FY 20-21 | FEB | 590 |
FY 20-21 | MAR | 2230 |
FY 20-21 | APR | 2210 |
FY 20-21 | MAY | 1120 |
FY 20-21 | JUN | 130 |
FY 21-22 | JUL | 990 |
FY 21-22 | AUG | 120 |
FY 21-22 | SEP | 540 |
FY 21-22 | OCT | 8810 |
FY 21-22 | NOV | 210 |
FY 21-22 | DEC | 240 |
FY 21-22 | JAN | 360 |
FY 21-22 | FEB | 110 |
FY 21-22 | MAR | 52 |
FY 21-22 | APR | 4410 |
FY 21-22 | MAY | 120 |
FY 21-22 | JUN | 3600 |
i need to highlight the maximum and minimum value in a matrix visual for each fiscal year seperately, having green color for maximum value and red color for minimum value
expected output:
Solved! Go to Solution.
@RAHULBANDI I created a color filter like below and used it in conditional formatting. See PBIX file attached below signature:
Color Formatter =
VAR __FY = MAX('Table'[FISCAL YEAR])
VAR __Value = SUM('Table'[SALE])
VAR __Table = SUMMARIZE(FILTER(ALL('Table'),[FISCAL YEAR] = __FY),[MONTH],"__Sales",SUM('Table'[SALE]))
VAR __Min = MINX(__Table,[__Sales])
VAR __Max = MAXX(__Table,[__Sales])
RETURN
SWITCH(TRUE(),
__Value = __Max,1,
__Value = __Min,2,
0
)
@RAHULBANDI I created a color filter like below and used it in conditional formatting. See PBIX file attached below signature:
Color Formatter =
VAR __FY = MAX('Table'[FISCAL YEAR])
VAR __Value = SUM('Table'[SALE])
VAR __Table = SUMMARIZE(FILTER(ALL('Table'),[FISCAL YEAR] = __FY),[MONTH],"__Sales",SUM('Table'[SALE]))
VAR __Min = MINX(__Table,[__Sales])
VAR __Max = MAXX(__Table,[__Sales])
RETURN
SWITCH(TRUE(),
__Value = __Max,1,
__Value = __Min,2,
0
)
User | Count |
---|---|
107 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |