Forum Discussion

deb_power123's avatar
deb_power123
Icon for Helper V rankHelper V
5 years ago

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

 

Need Help
Need help (Using direct query)
Need Help DAX
Need Help DAX measure

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    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 ()
    )
     
     

    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

    • deb_power123's avatar
      deb_power123
      Icon for Helper V rankHelper V
      5 years ago

      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

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        5 years ago

        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.