Forum Discussion
Scratching my head with trying to transform data.
Hi all,
I'm trying to get a set of data which results in a decimal place from 0 to 1 to use in a intensity heatmap.
The data I have is a list of desks say 1-30 and another column where we have desks which have been booked so desk 15 might have be in there 5 times but desk 2 might only have been booked 1 time.
How on earth do I start to piece together getting what I think is a percentage value of the most booked desk being 1 in the intensity column and ones that have not been booked being a 0
Any direction would be greatly appreciated!
Thanks
David
4 Replies
- timg
Solution Sage
Hi Mullz,
I'm not entirely sure how your dataset looks but if we can assume that there is a row for every desk with the corresponding reservationNo you could try something like this to get a value between 0 and 1 for every desk, based on the desk with the most reservations being 1:
PercentageBasedOnMax = DIVIDE(testtable[ReservationNo], CALCULATE(MAX(testtable[ReservationNo]),ALL(testtable)))Is that kinda what you are looking for? If not, could you add some more context to your question?
Best Regards,
Tim
Hi timg
Appreciate you getting back to me, yes that looks like it will do the trick, however when replicated it says that my 'intensity' column which is your 'ReservationNo' in the example can't be used in that way, is it because my reservationNo equivalent is being made up by another measure?
Here is the measure, can you combine the two?
Intensity = COUNTROWS(FILTER('Desk Reservations', 'Desk Reservations'[DeskText] in VALUES('Desks'[Title])))The above is basically trying to see how many times a desk appears in DeskText
Title being a complete list of all the desks and desk reservations being rows of desks which might be the same.
Sorry, I think I'm confusing myself now!
D
