Forum Discussion
Matrix Visual - Single 'Values' field over multiple columns with different decimal places
I am using a Matrix visual with unpivoted data (attribute-value pairs). I have the 'attribute' in the "Columns" field, and the "value" in the values field'
Quick example (with made up data!) has population and average age of countries. The population doesn't have any meaning with decimal places, but average age needs one or two decimal places.
I've tried "auto" units, but I still get two decimal places on the population. Is there any way for powerbi/matrix to just show whatever decimal places the raw data has?
Note: These still need to be numbers so the matrix can roll up rows into totals/averages, so I can't 'fake' it with a text column.
I could probably use a measure and manually do all of the rollup logic myself...but I was hoping for an easier/built-in solution.
6 Replies
Incipient , You can try a measure like
Measure =
Switch( Max(Table[Attr]) ,
"Population", format(sum(Table[Value]), "#,###") ,
"Avg Pop Age", format(Average(Table[Value]), "percent")
)
amitchandak Thanks for the suggestion!
Yeah a measure is my fallback, but it's very far from remotely good.
My data is a lot more complex than this, so the measure is not so straightfoward, and a functional issue is when users export data that has been converted to text with format(), the xlsx has all of the cells as 'text' not a number, so it heavily breaks usability in excel.
Incipient , You will have a lot of measures. You can pivot in power query
Incipient create individual measures as below for the population and avg age
Population = CALCULATE(SUM(Population[Value]),Filter(Population,Population[Attr]="Population")) Avg Age = CALCULATE(SUM(Population[Value]),Filter(Population,Population[Attr]="Avg age"))Place it in Matrix visual.
Go to visual format and under the specific column category under Values, you can select the measure and do change the decimal places to 0 from auto to population and for avg age change it to 2.
Accept the solution if it works.
Anonymous Again, a useful suggestion on this sample dataset, unfortuantely I have up to 100 'attributes', and I need the matrix to dynamically add/remove the columns based on filters/selection.
Example here would be if someone selected filtered on "Asia" the Matrix would only show "Japan" (and remove the other columns).
