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 | |
| March | 2.3 | 2.7 | 2.4 | 1.4 | 2.4 | 4.1 | ||
| Total | 2.7 | 2.6 | 2.3 | 1.5 | 2.5 | 2.4 | 2.9 | 6.8 |
But i need to present it this way
| 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.5 | 2.4 | 1.6 | 2.6 | 2.5 | 5.4 | ||
| March | 2.4 | 1.4 | 2.4 | 4.1 | ||||
| Total | 2.3 | 2.3 | 2.3 | 1.5 | 2.5 | 2.4 | 2.9 | 6.8 |
There are 2 dimensions, Months calendar and Harvest range (-3 to 3) wich is the periods before and after the month and 0 being the base month. And one measure, customer average transactions.
In order to evaluate the customer behaviour, the variation must be calculated using the same harvest range in each month. For example for February we must use the values (2.5, 2.4) vs (2.6, 2.5) wich is the range -2 to 2.
I am trying to do this, but with no luck
[Average Transaction] = DIVIDE(SUM(FACT_TRANSACTION_GROWTH[TRX]),DISTINCTCOUNT(FACT_TRANSACTION_GROWTH[CUSTOMER]))
MaxHarvest =
var Harvest = CALCULATE(MAX(HARVEST[MONTHS]), HARVEST[DAYS] = MAX(FACT_TRANSACTION_GROWTH[DAYS])) - 1
return IF(Harvest > 0, Harvest)
"I am substracting 1 because, the last month doesn't always have complete days."
Measure = IF(SELECTEDVALUE(HARVEST(MONTHS) >= MaxHarvest*-1 && SELECTEDVALUE(HARVEST(MONTHS) <= MaxHarvest,[Average Transaction] )))
This only result in this
| Month | 0 |
| January | 1.2 |
| February | 1.6 |
| March | 1.4 |
| Total | 1.5 |
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:
3 Replies
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
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:
