Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
solvisig
Advocate III
Advocate III

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
Anonymous
Not applicable

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

Anonymous
Not applicable

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?

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.