Scratching my head with trying to transform data.

[Mullz]

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

[timg]

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

[Mullz]

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

[timg]

ah oke so if I understand correctly you don't have the information summarized in the table but instead you have a table in which desk 1 for example occurs in 20 rows, meaning there were 20 reservations. In this case you would indeed calculate it with a measure instead of a column, which would require you to add a summarize in the calculation to get the totals for all desks. This would look something like the image below.

So you still have the DIVIDE, but instead of dividing column values you divide the COUNTROWS() measure (i simplified mine by just taking a countrows of the table) by the maxx number returned from a summarized table of countrows per desk.  

Hope that helps!

Regards,

Tim

[Mullz]

Apologies @timg I didn't mean can 'you' combine the two, it was more a general can you do that in the UI 😄