Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have a table location and I want to hide the total of seats used column[Total Used] and show only total for [%Occupied] in my visual .
I am able to do so using the below DAX but it works correctly only when there are multiple entires of building floor for a location but when it is single location then it is showing both total of seats used column[Total Used] and total for [%Occupied].
Below is the DAX to calculate and hide the Total Used values in the table matrix visual:
Total Used = IF(
HASONEVALUE( 'location'[seats used]),
SUM('location'[seats used]) & "|"
& SUM('location'[seats available]),
BLANK()
)
To find the percentage of seats occupied
%Occupied = SUM('location'[seats used])/SUM('location'[seats available])
My present visual looks like below :-
Here I want to get rid or hide the total of Used which is highlighted in yellow. I have handled that in the DAX query above but it is only working when there are multiple entries for building floor column corresponding to its location.
Input data :-
Date | Location | buildingfloor | seatsavailable | seatsused |
01.12.2020 | B1 | 1st floor | 20 | 10 |
01.12.2020 | B1 | 3rd floor | 21 | 11 |
01.12.2020 | B1 | 4th floor | 22 | 13 |
02.12.2020 | B2 | 3rd floor | 23 | 12 |
01.05.2021 | B3 | 7th floor | 20 | 20 |
01.05.2021 | B3 | 8th floor | 30 | 21 |
03.05.2021 | B3 | 2nd floor | 40 | 18 |
03.05.2021 | B4 | 8th floor | 25 | 6 |
04.05.2021 | B5 | 2nd floor | 21 | 2 |
Expected output:-
Could you please suggest how and what changes do i need to implement in the below DAX to hide the total of used column[highlighted in yellow]?
Kind regards
Sameer
Hi, @deb_power123
Please check the below picture and the link down below.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
hi @Jihwan_Kim
Thankyou for your reply, your logic does make sense but i am not sure why when i used your formula in the report with more date values and location values, I just got "|" symobols accross the visual.
It looks very weird as i used exactly the same formila you gave above. It is not showing any values and only | symbol . Could you please suggest why it happened and is there anyother way to do this approach ?
or if we can make the Occupied value in total say while color so that it mixes with background.Is that possible?
I am really failing to understand why it is not returning any values when I use your formula.All values suddenly disappeared.
Kind regards
Sameer
Hi, @deb_power123
Thank you for your feedback.
If it is OK with you, please share your sample pbix file, then I can try to look into it to find out the root cause of the issue.
Thanks.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
20 | |
19 | |
16 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |