Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
deb_power123
Helper V
Helper V

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

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.

 

Error_DB.JPG

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

 

3 REPLIES 3
Jihwan_Kim
Super User
Super User

Hi, @deb_power123 

Please check the below picture and the link down below.

 

Picture2.png

 

Total Used =
IF (
ISINSCOPE ( 'location'[buildingfloor] ) || ISINSCOPE ( location[Location] ),
SUM ( 'location'[seats used] ) & "|"
& SUM ( 'location'[seats available] ),
BLANK ()
)
 
 

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.


Go to My LinkedIn Page


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.

 

e1.JPG

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.


Go to My LinkedIn Page


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.