Forum Discussion
Show values on Matrix based on range dimension
Hi, i need help with this visual I have this matrix Month -3 -2 -1 0 1 2 3 Total January 2.3 2.2 2.5 1.2 2.7 2.6 2.9 6.9 February 2.2 2.5 2.4 1.6 2.6 2.5 5.4 ...
Solved it;
Solution:
1st - Create a measure to calculate the maximun month to harvest
Max Harvest =var Months= CALCULATE(MAX(HARVEST[MONTHS]),HARVEST[DAYS] = MAX(FACT_TRANSACTION_GROWTH[DAYS]))return IF(Months> 0,Months)2nd - Create same measure, but remove harvest filtersMax Harvest Fixed = CALCULATE([Max Harves],REMOVEFILTERS(HARVEST))3rd - Create a measure to calculate the minimun month to harvestMin Harvest =var Months= CALCULATE(MIN(HARVEST[MONTHS]),HARVEST[DAYS] = MAX(FACT_TRANSACTION_GROWTH[DAYS]))return IF(Months < 0 && Months > [Max Harvest Fixed] * -1,Months)4th - Create the final measureMeasure =var Months = CALCULATE(MAX(HARVEST[MONTHS]),HARVEST[DAYS] = MAX(FACT_TRANSACTION_GROWTH[DAYS]))return IF(Months >= [Min Harvest] && Months < [Max Harves Fixed],[Average Transaction])The visual:
Anonymous can you share the sample data with the expected output?
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
This is the fact Table
| Month | Customer | Days | Amount |
| January | A | 0 | 3 |
| January | A | -13 | 6 |
| January | A | -65 | 5 |
| January | A | -84 | 5 |
| January | A | -65 | 4 |
| January | A | 58 | 2 |
| January | B | 0 | 1 |
| January | B | 49 | 5 |
| January | B | -92 | 1 |
| January | B | -78 | 3 |
| January | B | -86 | 6 |
| January | B | -56 | 3 |
| January | C | 0 | 6 |
| January | C | 15 | 6 |
| January | C | -5 | 6 |
| January | C | 68 | 3 |
| January | C | -57 | 3 |
| January | C | -96 | 5 |
And this is the dimension table
| Days | Months |
| -42 | -2 |
| -41 | -2 |
| -29 | -1 |
| -28 | -1 |
| -27 | -1 |
| -26 | -1 |
| -25 | -1 |
| -24 | -1 |
| -23 | -1 |
| -22 | -1 |
| 0 | 0 |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 1 |
| 8 | 1 |
| 9 | 1 |
| 10 | 1 |
| 11 | 1 |