Incorrect values in matrix totals/subtotals

[Anonymous]

Hello,

I'm trying to reformat the columns of my matrix to show the correct value with the respective unit. The column on the left "Spending %" is the raw data from my data source and the right column "Formatted measure" is my formatting column. My current issue is that the values within the hierarchy are shown correctly, but the row subtotals and the total values are not showing correctly. I'm using this formula right now: 

Formated measure = IF(VALUE(Table[Spending %]) = 0.00, "0%", FORMAT(Table[Spending %], "###%"))

To get this output:

I would greatly appreciate any help in either reformatting my columns using this method or suggesting an easier method. 

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

You can change the [Spending %] to pencentage as below instead of using FORMAT function. As it will make your data to text type.

[Anonymous]

Hi @v-frfei-msft,

Thanks! This solution worked for columns with all numerical values. Although, some of my columns have the value "Infinity" as text - how would I format the column in this case? I still want to display the percentages for values that are not Infinity. 

  

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907