How to hide specific columns but display only total on Matrix

[Anonymous]

The above matrix is how the original one looks like. I would like to display only quantity sold columns and total current inventory column but hide all current inventory columns by date. I would like to display only total current inventory. 

Below matrix is how i want it to be looked like.

I tried using *word wrap* from *column headers* to hide all current inventory columns except the total one. However, if i export data, it will show all current inventory columns by date. Furthermore, if i expand all down one level in the hierarchy to month from quarter, i have to manually hide curren inventory columns... ALSO,  the below matrix size looks ugly compared to upper one.

[v-zhenbw-msft]

Hi @Anonymous ,

Sorry for that if you put the Qty sold and inventory in Values, Matrix table cannot show the Total only, it will show the value in each column.

Maybe you can use the one matrix table cover another matrix table.

If you have any question, please kindly ask here and we will try to resolve it.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[AllisonKennedy]

Without creating a custom measure you cannot hide the columns, so I can't fix the export part of your question.

As you said, you can turn off Word Wrap under Column Headers. You can also turn off Auto-size Column Width so that once you have set the width of each column it will stay even when using drill up and drill down.

Other option could be to turn off totals in this matrix and have them in an adjacent table or multicard visual. You can use Edit interactions to make these visuals interact with each other how you would like.

Final option won't give pretty results, but you do have the option under Field Formatting to make the values white on white background while turning off the apply to totals and subtotals so that you can only see the totals.

[FarhanAhmed]

You can create measure like this.

_Amount = IF(HASONEVALUE('Date'[Quarter]),SUM(Qty[Qty]),SUM(Qty[Amount]))

This will return Qty when you put it in Quarter otherwise it will show Amount if more than 1 quarters are selected.... 

you can replace it with your columns to check the results.

[Anonymous]

Hi @Anonymous 

Hiding a column is not an option.

You can create a measure to sum up the total and display.

Total_Sku_year = CALCULATE(sum(table[quantity_sold],Allexcept(table,table[sku],table[Year_from_date]))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Regards,
Pranit

[amitchandak]

@Anonymous , I doubt there is an option to hide a column from the total. But you can make it blank using is filtered.

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/