Calculation Group format string expression mixing numbers and percentages help

[Anonymous]

The problem is that I want to get the Matrix and Table to recognise a figure as either a Number or a Percentage

in one column rather than having to see it in 2 columns.

For example. The matrix visualisation is currently showing:

Trend    Actual %             Target %             Actual (number)             Target (number)

What I would like to show on the matrix is  the following (this is a mock-up from excel)

The Tables show:

What I would like to show on the table is  the following (this is a mock-up from excel)

I do have a “tooltip matrix which is working and if I hover over the table it does show the following:

The Mainsheet fact table does have a column showing Number of Percentage

The matrix does use the No.s or % test Calculation Group (precedent group 2) – and  “Name” is in the column

 This is set as precedent group 2

Note: there is another Calculation group “Matrix (precent group is 3) but I have not used this yet

Calc Items	Expression	Format String Expression
Current	SELECTEDMEASURE()
Percent	CALCULATE (     SELECTEDMEASURE (),     FILTER (         Mainsheet_1,         [Number or Percentage] = "%"     ) )	“0%”
Number	CALCULATE (    SELECTEDMEASURE (),    FILTER (         Mainsheet_1,        [Number or Percentage]  = "No."    ))	"0"

I think my issue is around the format string expression

I will try to find a way of linking the pibx as it will not work from my current work domain.

[Anonymous]

See dropbox links

Excel

https://www.dropbox.com/scl/fi/jl93h577gpogoh6i6r94d/Performance-report-data-random-1-just-provider-...

Power Bi

https://www.dropbox.com/s/tpde5yduzs3drbs/Performance%20report%20Data%20Random.pbix?dl=0

[Anonymous]

See main column headings from spreadsheet

KPI Index	Sort Order	Service	Provider	KPI Summary	Number or Percentage	Polarity	Date	Year & Qtr	Actual	Target	Target Agreed
22	9	Service "C"	Provider "C"	Length of Time in Treatment >26 week	No.	Lower is better	30/06/2021	2021/22 Q1	21	26	26
22	10	Service "C"	Provider "C"	Length of Time in Treatment >26 week	No.	Lower is better	30/09/2021	2021/22 Q2	22	26	26
22	11	Service "C"	Provider "C"	Length of Time in Treatment >26 week	No.	Lower is better	31/12/2021	2021/22 Q3	18	26	26
22	12	Service "C"	Provider "C"	Length of Time in Treatment >26 week	No.	Lower is better	31/03/2022	2021/22 Q4	18	26	26
28	9	Service "C"	Provider "C"	Conversion Rate	%	Higher is better	30/06/2021	2021/22 Q1	20.36%	0.75	75%
28	10	Service "C"	Provider "C"	Conversion Rate	%	Higher is better	30/09/2021	2021/22 Q2	37.88%	0.75	75%
28	11	Service "C"	Provider "C"	Conversion Rate	%	Higher is better	31/12/2021	2021/22 Q3	89.75%	0.75	75%
28	12	Service "C"	Provider "C"	Conversion Rate	%	Higher is better	31/03/2022	2021/22 Q4	57.97%	0.75	75%

[v-yanjiang-msft]

Hi @Anonymous ,

Are Act%, Tar%, Art and Tar all your columns? Why there're not Act% and Tar% in the last spreadsheet.

Best Regards,
Community Support Team _ kalyj

[Anonymous]

Hi @v-yanjiang-msft. I have now added a clearer spreadsheet and included links to dropbox excel and pibx file.

Link to excel file 

Link to PIBX 

The main columns are Actual and Target. In the "Matrix" the Act% Tar% Act and Tar come from the Calculation Group "No.s or % test" and I just use the "Name" in the "Column" of the visual.
I then went into format painter, into a column header and changed the font colour from black to white (so you could not see the column header colour) I then added 5 x text boxes just for visual display (Trend, Act%, Tar%, Act, Tar)