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

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/

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors