Apply same dynamic formatting method for a multitude of columns in table

Question

Hi&nbsp;&nbsp;I have a well prepared source table listing down individual's monthly KPI performance.&nbsp; The PBI report only need to show the source table.&nbsp; After the source table is imported, the data looks like Table 1&nbsp; and we need final look like Table 2.&nbsp; As shown, for some of the KPIs, we need to show in % format.&nbsp; &nbsp;I have more than 80 columns (different year month) in source table and each column need to apply this formatting method.&nbsp; Thinking of using calculation groups in tablur editor but all the columns are columns in source table (not measure, to make a measure for each column, it is labour-intensive).&nbsp; &nbsp;&nbsp;Any advice how can I quickly apply same formatting on multiple columns?&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;tamerj1&nbsp; Thanks for the solution, it works, however it is slow for my actual dataset&nbsp; it takes close to half minute to refresh the matrix.&nbsp; Any suggestion to speed up it.?&nbsp; &nbsp;my dax is :&nbsp;&nbsp;mea = var t = 'KPI_PERC_F'[KPI]return IF ( SELECTEDVALUE ( 'TBL'[KPI] ) IN t, FORMAT ( SELECTEDVALUE ( TBL[Value] ), "0.0%" ),selectedvalue(TBL[Value]) )In 'KPI_PERC_F'[KPI], there are about 250 data.&nbsp;

tamerj1 · Answer

Hi&nbsp;Anonymous&nbsp;The best approach is to unpivot the columns using power query. It is possible to unpivot the data using DAX but this will require to create a new table and hence duplicate&nbsp;the data.In power query editor select all the "Year Month" columns and select unpivotRename the columns inside the code as convenientAdd the value column as a measure - example:KPI Value = 
IF ( 
    SELECTEDVALUE ( Table1[KPI] ) IN { 2, 3 },
    FORMAT ( SELECTEDVALUE ( Table1[Value] ), "Percent" ),
    SELECTEDVALUE ( Table1[Value] )
)&nbsp;Create your matrix: "KPI" on rows, "Year Month" on columns and the measure on valuesHere is a sample file for your reference&nbsp;https://www.dropbox.com/t/EcbFqwYlnAqOF54F&nbsp;

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,
This feature is not currently supported by Power BI.
Please vote here:&nbsp;Select all columns / select more than one column for field and conditional formatting&nbsp;
&nbsp;
If I have misunderstood your meaning, please provide more details and desired output.
&nbsp;
Best Regards
Community Support Team _ Polly
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

tamerj1 · Answer

Anonymous&nbsp;Could be related to the visual rather than the code. However you can use variables&nbsp;MEA =
VAR PercentKPIs =
    VALUES ( 'KPI_PERC_F'[KPI] )
VAR SelectedKPI =
    SELECTEDVALUE ( 'TBL'[KPI] )
RETURN
    IF ( SelectedKPI IN PercentKPIs, FORMAT ( SelectedKPI, "0.0%" ), SelectedKPI )

anonymous · Answer

Visual part it is less than 500ms on perforamnce analyzer. variables do help, the first variable does not impove the performance a lot but the second variable SelectedKPI on your code reduce the dax query time from 75 s to 16 s.&nbsp; &nbsp; &nbsp;one further question: if user want to view selected column say (Mar in my table above) all in % form regardless of the row content. Any modification we can do?&nbsp; &nbsp;Thanks

Forum Discussion

Apply same dynamic formatting method for a multitude of columns in table

8 Replies

Related content

Dynamic Formatting Error

Dynamic formatting

Dynamic number format

Dynamic String Format

dynamic conditional formatting