Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Anonymous
Not applicable

Show values on Matrix based on range dimension

Hi, i need help with this visual

 

I have this matrix 

Month-3-2-10123Total
January2.32.22.51.22.72.62.96.9
February2.22.52.41.62.62.5 5.4
March2.32.72.41.42.4  4.1
Total2.72.62.31.52.52.42.96.8

 

But i need to present it this way

Month-3-2-10123Total
January2.32.22.51.22.72.62.96.9
February 2.52.41.62.62.5 5.4
March  2.41.42.4  4.1
Total2.32.32.31.52.52.42.96.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

Month0
January1.2
February1.6
March1.4
Total1.5

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 filters
Max Harvest Fixed = CALCULATE([Max Harves],REMOVEFILTERS(HARVEST))
 
3rd - Create a measure to calculate the minimun month to harvest
Min 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 measure
Measure  =
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: 
Jaime81_0-1662417378696.png

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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 filters
Max Harvest Fixed = CALCULATE([Max Harves],REMOVEFILTERS(HARVEST))
 
3rd - Create a measure to calculate the minimun month to harvest
Min 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 measure
Measure  =
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: 
Jaime81_0-1662417378696.png

 

parry2k
Super User
Super User

@Anonymous can you share the sample data with the expected output?

 

Follow us on LinkedIn and YouTube.gif 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

This is the fact Table

MonthCustomerDaysAmount
JanuaryA03
JanuaryA-136
JanuaryA-655
JanuaryA-845
JanuaryA-654
JanuaryA582
JanuaryB01
JanuaryB495
JanuaryB-921
JanuaryB-783
JanuaryB-866
JanuaryB-563
JanuaryC06
JanuaryC156
JanuaryC-56
JanuaryC683
JanuaryC-573
JanuaryC-965

 

And this is the dimension table 

DaysMonths
-42-2
-41-2
-29-1
-28-1
-27-1
-26-1
-25-1
-24-1
-23-1
-22-1
00
11
21
31
41
51
61
71
81
91
101
111

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.