cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mullz
Frequent Visitor

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 4
timg
Solution Sage
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)))

timg_0-1645022765097.png

Is that kinda what you are looking for? If not, could you add some more context to your question?

Best Regards,

 

Tim

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Mullz
Frequent Visitor

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
Solution Sage
Solution Sage

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.  

timg_0-1645026145013.png

 

Hope that helps!

Regards,

 

Tim





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Mullz
Frequent Visitor

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors