Forum Discussion
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
19 Replies
Hi,
If you don't want to see particular column for different categories in the matrix then there is one solution.
For example,
From the above matrix and I want to see only
Current FY -> Actual, Budget
May 2020 -> Actual
May 20201 -> Actual, Budget and VarSo, 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 =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".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']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:
Thank You! I hope you will like it.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.
Would anyone know if it is possible?
Thank you!
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 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
Hi Anonymous,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
