cancel
Showing results for
Did you mean:

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

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.

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
Super User

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.

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.

Helper V

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

Super User

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors