Forum Discussion
How to format value based on text value in Pivot Table
How does one format the value outputs in a Pivot Table/Matrix within PowerBI based on the text values within the row?
For example, lets imagine I had this table in Excel. I could just click on each value and format it to either currency, or Percentage, of course I could also do this via a formula.
| Label | Value |
| New Business | £1,000,000 |
| Ratio | 32.56% |
| Renewals | £56,000,000 |
| Lapses | -£11,000,000 |
| Rate | 10.50% |
How would I achieve this in Power BI? Label in the Rows Section and Sum of Value in the values section. I'm sure there must be a simple way to do this, via DAX perhaps, but for the life of me I cannot find it.
Saarek , if these are measures, Switch values to row and they will show their own format.
If they are row values based on each value you have to use the format , assuming value is measure
Switch( True(),
Max(Table[Label]) in {"Ratio","Rate"} , format([Value], "percent" ) ,
format([Value], "currency" )
)
or refer
https://www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/
1 Reply
Saarek , if these are measures, Switch values to row and they will show their own format.
If they are row values based on each value you have to use the format , assuming value is measure
Switch( True(),
Max(Table[Label]) in {"Ratio","Rate"} , format([Value], "percent" ) ,
format([Value], "currency" )
)
or refer
https://www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/