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