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

hiding columns in Matrix

Hi there,

 

Is it possible to hide columns and keep the total in a matrix. You can do something similar in SSRS by just adding a total at the end of the report.

 

In the image below, can I hide "Opportunity" in the matrix and keep the total?

 

Thank you

 

Capture.PNG 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi Frank,

 

I was able to solve the problem by turning off word wrap in the matrix setting and dragging the field I wanted to hide.

 

Capture.PNG

View solution in original post

19 REPLIES 19
Pragati_Sharma
Frequent Visitor

Hi,

If you don't want to see particular column for different categories in the matrix then there is one solution.
For example, 

Pragati_Sharma_0-1657961065000.png
From the above matrix and I want to see only
Current FY -> Actual, Budget
May 2020 -> Actual
May 20201 -> Actual, Budget and Var

So, for this I have created one another table where I am taking only those columns which I want to see in the matrix. As shown below 

Categroy Table = 

Pragati_Sharma_2-1657961350018.png

You can make this table by just entering data and loading into the power bi. 

Next step is to create relationship between Sales table and Category table. 
My sales table has a calculated column named "FY Bucket". 

Pragati_Sharma_3-1657961603105.png

 

FY bucket =
SWITCH(TRUE(),

Sales[Month No] <= 5 && Sales[Year] = 2020,"MAY 2020",
Sales[Month No] <= 5 && Sales[Year] = 2021,"MAY 2021",
Sales[Month No] > 5 && Sales[Year] = 2021 || Sales[Year] = 2020 ,"Current FY","Others")

And now we will create the relationship between Category['FY'] & Sales['FY bucket']

Pragati_Sharma_5-1657961712442.png

Note that, as both table has multiple values in the column it will display Many to Many relationship only. 
To avoid many-to-many relationship you can create bridge table. 

Next step is calculating Actual, Budget and Var for corresponding columns. Here, I am creating measure for that, it's formula is given below. 

Sales Measure =
SWITCH(TRUE(),

SELECTEDVALUE('Category'[FY]) = "Current FY" && SELECTEDVALUE('Category'[Cal]) = "Actual",
CALCULATE(SUM(Sales[Actual]),
Sales[FY bucket] = "Current FY"),

SELECTEDVALUE('Category'[FY]) = "Current FY" && SELECTEDVALUE('Category'[Cal]) = "Budget",
CALCULATE(SUM(Sales[Budget]),
Sales[FY bucket] = "Current FY"),

SELECTEDVALUE('Category'[FY]) = "MAY 2020" && SELECTEDVALUE('Category'[Cal]) = "Actual",
CALCULATE(SUM(Sales[Actual]),
Sales[FY bucket] = "MAY 2020"),
 
SELECTEDVALUE('Category'[FY]) = "MAY 2021" && SELECTEDVALUE('Category'[Cal]) = "Actual",
CALCULATE(SUM(Sales[Actual]),
Sales[FY bucket] = "MAY 2021"),

SELECTEDVALUE('Category'[FY]) = "MAY 2021" && SELECTEDVALUE('Category'[Cal]) = "Budget",
CALCULATE(SUM(Sales[Budget]),
Sales[FY bucket] = "MAY 2021"),

SELECTEDVALUE('Category'[FY]) = "MAY 2021" && SELECTEDVALUE('Category'[Cal]) = "Var",
CALCULATE(SUM(Sales[Var]),
Sales[FY bucket] = "MAY 2021")
)

Put your measure as Values into the matrix. 
And here is the result: 

Pragati_Sharma_6-1657962091174.pngPragati_Sharma_8-1657962394637.png


Thank You! I hope you will like it. 

Mine is a similar example; however, the string columns are measures. Would you know how to go about removing just one column similar to this example? 

Hi everyone, 

 

This is a great solution!

For my situation though, I have a slightly different scenario.

 

I wish to show as null/blank for the "target" column values only when the third row in the matrix table is drilled down/shown.

This is because the target only applies to the first two rows, and is not applicable to the third row.

GregGiamz_1-1681358952213.png

 

Would anyone know if it is possible?

Thank you!

 

 

 

v-frfei-msft
Community Support
Community Support

Hi @Anonymous,

 

There is no such an visibility option that supports to conditionally hide/show column in Matrix. One option is to hide or show the values in the column by creating measures, but column headers are still appeared even there is no data. Reference: https://community.powerbi.com/t5/Desktop/How-can-i-hide-Column/td-p/110087

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Thank you Frank, isnt completely what am looking for but thats very close to it. Thanks once again 

Hi @Anonymous,

 

Does that make sense? If so, kindly mark my answer as a solution to close the case.


Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Hi Frank,

 

I was able to solve the problem by turning off word wrap in the matrix setting and dragging the field I wanted to hide.

 

Capture.PNG

Drag where?

If we squeeze columns, that will hide that column from report users but users who are exporting it to excel will still sble to view the hidden fields. Is there a way to hide from exported file as well.

hi 

iam facing the same issue but iam not able to find the word wrap filed in the latest version of power bi desktop can please suggest me which field i need to see.

jyothi12_0-1661408672025.png

 

It is called Text Wrap now--the easiest way I'm finding to deal with the new layout is using the search bar in the formatting section.

AGhyst_0-1661447222439.png

 

Hi, thank you for this hint. It is a nice solution for hiding columns and only showing the totals in a matrix visual. But I think this is only a workaround and it would great/nice to have such a feature available in Power BI Desktop in the settings of the Matrix visual. Btw. if you publish the report the to Power BI Service the settings are gone and the columns are all visible again. So at the end this workaround is only usable if you are not publishing the reports to Power BI Service. 

 

Maybe a switch in the setting for "specific columns" named "Only show totals" or something else would be great. If this switch is selected for a specific column the values for this column will not be shown and only the total will be visible then. 

 

Or in the visual settings itself, there we have at the moment three sections:

- Rows

- Columns

- Value

 

Maybe it is possible to add here two additional sections:

- Sub-Totals

- Totals

 

By adding the data th these sections you could then control which data is shown in which part of the visual.

 

I think this could be a very usefull feature request.

 

Regards,

Frank

Hi,

I have similar situation-want to hard hightlighted in yellow color column. Want to see total. I try to follow your solution but lost. 

ABC11_0-1651088800917.png

 

under which categary I should go for "turning off word wrap " and dragging field

ABC11_2-1651089076745.png

Please

Thanks

 

hi 

iam also facing the same issue can you please help me .i need to hide the order cases i want ordercases in total field.below i have marked i need to hide .

 

jyothi12_0-1661407934455.png

 

Someone else will probably have a better solution but this reminds me of one that I had a problem with because I needed 3 year's sales but only current year's budget.   I found the cleanest solution was to write measures for each year and one for the budget then put them into a table.   Had the advantage of giving me measures I could use for other calculations

AGhyst_1-1661447919585.png

So I think you could write a CALCULATE for each of your fill rates and then a total for your cases to build something similar. 🙂

Hi,

how to write the calculate function fillrate is the calculated measure and ordercases is a column periods also columns .

fillrate=sum of ordered cases/shipped cases 

i have calculated 

I'm not great at DAX but this is what I would try.  Essentially you can use CALCULATE as a filter. You would need a new measure for each of the fill rate columns you want and you can build it using the fillrate you already did.  I'm not sure what the name of the column field is so...

 

8x1 Fill Rate=CALCULATE(fillrate, [Field]="8x1")

8x2 Fill Rate=CALCULATE(fillrate, [Field]="8x2")

8x3 Fill Rate=CALCULATE(fillrate, [Field]="8x3")

8x4 Fill Rate=CALCULATE(fillrate, [Field]="8x4")

 

Then you would need to make a measure for your Ordered Cases (not sure if you have any filters on cases but if so you could use calculate to get it right).

Ordered Cases Total = SUM[ordered cases]

 

If you want the Total Fill Rate at the end you will need one more measure

Total Fill Rate=CALCULATE(fillrate)

 

Then drop all the measures onto a table in the order you want to see them.   Depending on how your calculations work with your data it can take some fine tuning.  Hopefully, that works--like I said I'm no expert lol.

Just a note to say thank you.  This solved a issue that was driving me insane

Nice little workaround with the hiding of the column. 👍  Helped me out.

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.