cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
deb_power123
Helper IV
Helper IV

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
Community Champion
Community Champion

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 accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

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 accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.