topic Re: How to hide total for a specific column in a table matrix using DAX? in DAX Commands and Tips

How to hide total for a specific column in a table matrix using DAX?

deb_power123 — Sat, 22 May 2021 06:07:37 GMT

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

Re: How to hide total for a specific column in a table matrix using DAX?

Jihwan_Kim — Sat, 22 May 2021 09:07:59 GMT

Hi, deb_power123

Please check the below picture and the link down below.

Total Used =

IF (

ISINSCOPE ( 'location'[buildingfloor] ) || ISINSCOPE ( location[Location] ),

SUM ( 'location'[seats used] ) & "|"

& SUM ( 'location'[seats available] ),

BLANK ()

)

https://www.dropbox.com/s/ekzwwmb8vmocef0/deb%20power.pbix?dl=0

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

Re: How to hide total for a specific column in a table matrix using DAX?

deb_power123 — Sat, 22 May 2021 15:01:16 GMT

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

Re: How to hide total for a specific column in a table matrix using DAX?

Jihwan_Kim — Sun, 23 May 2021 03:14:33 GMT

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.