Formatting value flow

[S4_1993]

Hi,

I want to format the matrix as mentioned in the below matrix.

KPI	ACT	vs PY	vs FC
Turnover	100	2	3
Sales Growth %	2.0%	1.8%	1.9%
Price Growth %	0.5%	0.4%	0.5%
Vol Groth %	1.5%	1.4%	1.5%
Gross Profit	60	5	5.0%
Gross Profit %	60.0%	390 bps	390 bps

Please help me how to format the matrix.

Regards,

Shyam

[v-xuding-msft · Accepted solution]

Hi @S4_1993 ,

Firstly, you need to unpivot the columns: ACT, vs PY and vs FC.  You could do it in Power Query or create a new table with DAX. Please try this:

Table 2 = UNION(
        SELECTCOLUMNS('Table',"KPI",'Table'[KPI],"Attribute","ACT","Values",'Table'[ACT]),
        SELECTCOLUMNS('Table',"KPI",'Table'[KPI],"Attribute","vs FC","Values",'Table'[vs FC]),
        SELECTCOLUMNS('Table',"KPI",'Table'[KPI],"Attribute","vs PY","Values",'Table'[vs PY])
)

Measure = 
var KPI_ = SELECTEDVALUE('Table 2'[KPI])
VAR Values_ = SUM('Table 2'[Values])
VAR Attribute = MAX('Table 2'[Attribute])
return
SWITCH(TRUE(),
KPI_="Price Growth %" || KPI_="Sales Growth %" || KPI_="Vol Groth %", FORMAT(Values_,"0.0%"),
KPI_="Gross Profit" &&  Attribute= "vs FC" ,FORMAT(Values_,"0.0%"),
KPI_="Gross Profit %"&& Attribute = "ACT",FORMAT(Values_,"0.0%"),
KPI_="Gross Profit %"&& Attribute <> "ACT",CONCATENATE(Values_,"bps"),
Values_
)

 

Note: The measure returns text values. Because we use the function format and concatenate. And they both returns text. If you want to use the results to calculate, you need to use the original data rather than this measure. For now, it is not supported to use different formats in one measure and show the number type at the same time.

You could vote up the idea to get the feature fixed as soon as possible.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9588453-conditional-formatting-th...

[amitchandak]

@S4_1993 , you may have to create a custom table and display

union(
summarize(table,"Measure","Sales Growth", "ACT",[Sales Growth], "PY",[Sales Growth PY],  "FC",[Sales Growth FC]),
summarize(table,"Measure","Price Growth", "ACT",[Price Growth], "PY",[Price Growth PY],  "FC",[Price Growth FC])
)