cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate II
Advocate II

Hide values in table but still count them in Total

Is it possible to hide values in a matrix or table if sertain conditions are met.

I want to hide values for A and B if A is less than 10 for any row in type X or Y but still show the total:

 

I have this table:
ExampleExample

 

And this is the resault I´m looking for:

 

Expected resaultExpected resault

 

I´m not finding any solution on how to solve this. Any ides?

 

 

Underlying data:

3.PNG

4 REPLIES 4
Resolver II
Resolver II

You should be able to achieve what you want by playing around with the conditional formatting. Change the font colour to white for each of your fields based on the value of A (e.g. if A is less than 10). You'll need to 'Format by Rules' and for each field (A & B) used Based on field as A

Notice how in my image I've selected Vol (STD) TY as my field to change the colour font, but basing it on the value to Vol (STD) LY. Adjust the greater than or equal to accordingly and make the colour white.

 

image.png

I thought of that first but then I have another issue with the right click context menu.
Users can just right click and copy the value to see it.

4.PNG

 

Is there a way to disable this context menu?

Looks like they've just released that feature and haven't made mention of a disable option so it's not looking good that there is a way.

https://powerbi.microsoft.com/da-dk/blog/power-bi-desktop-september-2018-feature-summary/

Advocate II
Advocate II

Hello,

One option would be to create a measure that

a) hides the value if it doesn't meet the conditions and is a single value [i.e. using the HASONEVALUE() function] and

b) has a variable that summarizes the data without using that filter and displays the total without the filters if it is a subtotal field [i.e. using the sumx function for the summarized table in the false section of the HASONEVALUE() function]. 

 

Here's the concept from a different use case:

m_Total 2 = 
VAR __table = SUMMARIZE('Table1',[Category1],"__value",[m_Single])
RETURN
IF(HASONEVALUE(Table1[Category1]),[m_Single],SUMX(__table,[__value]))

--Since you'd want the total on both the x and y axis you may need an AND statement so it would be HASONEVALUE(Table[type]) && HASONEVALUE(Table[id])

 

It's convuleted but may work.  More details are here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/553537

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors