Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I have a well prepared source table listing down individual's monthly KPI performance. The PBI report only need to show the source table. After the source table is imported, the data looks like Table 1 and we need final look like Table 2. As shown, for some of the KPIs, we need to show in % format. I have more than 80 columns (different year month) in source table and each column need to apply this formatting method. 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).
Any advice how can I quickly apply same formatting on multiple columns?
@Anonymous
Here is the sample file updated as requested https://we.tl/t-lqNI1fBWg8
KPI Value =
IF (
SELECTEDVALUE ( Table1[KPI] ) IN { 2, 3 },
FORMAT ( SELECTEDVALUE ( Table1[Value] ), "Percent" ),
IF (
SELECTEDVALUE ( Table1[Year Month] ) IN { "202202", "202203" },
SELECTEDVALUE ( Table1[Value] ) & "%",
SELECTEDVALUE ( Table1[Value] )
)
)
Hi @Anonymous ,
This feature is not currently supported by Power BI.
Please vote here: Select all columns / select more than one column for field and conditional formatting
If I have misunderstood your meaning, please provide more details and desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
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 the data.
In power query editor select all the "Year Month" columns and select unpivot
Rename the columns inside the code as convenient
Add the value column as a measure - example:
KPI Value =
IF (
SELECTEDVALUE ( Table1[KPI] ) IN { 2, 3 },
FORMAT ( SELECTEDVALUE ( Table1[Value] ), "Percent" ),
SELECTEDVALUE ( Table1[Value] )
)
Create your matrix: "KPI" on rows, "Year Month" on columns and the measure on values
Here is a sample file for your reference https://www.dropbox.com/t/EcbFqwYlnAqOF54F
Hi @tamerj1 Thanks for the solution, it works, however it is slow for my actual dataset it takes close to half minute to refresh the matrix. Any suggestion to speed up it.? my dax is :
@Anonymous
Could be related to the visual rather than the code. However you can use variables
MEA =
VAR PercentKPIs =
VALUES ( 'KPI_PERC_F'[KPI] )
VAR SelectedKPI =
SELECTEDVALUE ( 'TBL'[KPI] )
RETURN
IF ( SelectedKPI IN PercentKPIs, FORMAT ( SelectedKPI, "0.0%" ), SelectedKPI )
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. 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? Thanks
Please clarify further
I mean if we want data in selected column all in percentage format (for example 202202 and 202203 these two column, highlighted value to be shown in percentage), how can we modify the DAX. In short, the format varies with both rows and columns
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |