Calculation Group format string expression mixing numbers and percentages help

Question

The problem is that I want to get the Matrix and Table to recognise a figure as either a Number or a Percentagein one column rather than having to see it in 2 columns.&nbsp;For example. The matrix visualisation is currently showing:Trend &nbsp;&nbsp; Actual %&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Target %&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Actual (number)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Target (number)&nbsp;What I would like to show on the matrix is &nbsp;the following (this is a mock-up from excel)&nbsp;The Tables show:&nbsp;What I would like to show on the table is &nbsp;the following&nbsp;(this is a mock-up from excel)&nbsp;&nbsp;I do have a “tooltip matrix which is working and if I hover over the table it does show the following:&nbsp;&nbsp;&nbsp;The Mainsheet fact table does have a column showing Number of PercentageThe matrix does use the No.s or % test Calculation Group (precedent group 2) – and &nbsp;“Name” is in the column&nbsp;&nbsp;This is set as precedent group 2Note: there is another Calculation group “Matrix (precent group is 3) but I have not used this yet&nbsp;&nbsp;Calc Items&nbsp;Expression&nbsp;Format String ExpressionCurrent&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECTEDMEASURE()&nbsp;PercentCALCULATE (&nbsp;&nbsp;&nbsp; SELECTEDMEASURE (),&nbsp;&nbsp;&nbsp; FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Mainsheet_1,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Number or Percentage] = "%"&nbsp;&nbsp;&nbsp; ))&nbsp;“0%”NumberCALCULATE (&nbsp;&nbsp;&nbsp; SELECTEDMEASURE (),&nbsp;&nbsp;&nbsp; FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Mainsheet_1,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Number or Percentage]&nbsp; = "No."&nbsp;&nbsp;&nbsp; ))&nbsp;"0"&nbsp;I think my issue is around the format string expression&nbsp;I will try to find a way of linking the pibx as it will not work from my current work domain.

v-yanjiang-msft · Answer

Hi&nbsp;Anonymous&nbsp;,
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 · Answer

See main column headings from spreadsheetKPI IndexSort OrderServiceProviderKPI SummaryNumber or PercentagePolarityDateYear &amp; QtrActualTargetTarget Agreed229Service "C"Provider "C"Length of Time in Treatment &gt;26 week&nbsp;No.&nbsp;Lower is better30/06/20212021/22 Q12126262210Service "C"Provider "C"Length of Time in Treatment &gt;26 week&nbsp;No.&nbsp;Lower is better30/09/20212021/22 Q22226262211Service "C"Provider "C"Length of Time in Treatment &gt;26 week&nbsp;No.&nbsp;Lower is better31/12/20212021/22 Q31826262212Service "C"Provider "C"Length of Time in Treatment &gt;26 week&nbsp;No.&nbsp;Lower is better31/03/20222021/22 Q4182626289Service "C"Provider "C"Conversion Rate%Higher is better30/06/20212021/22 Q120.36%0.7575%2810Service "C"Provider "C"Conversion Rate%Higher is better30/09/20212021/22 Q237.88%0.7575%2811Service "C"Provider "C"Conversion Rate%Higher is better31/12/20212021/22 Q389.75%0.7575%2812Service "C"Provider "C"Conversion Rate%Higher is better31/03/20222021/22 Q457.97%0.7575%

anonymous · Answer

See dropbox linksExcelhttps://www.dropbox.com/scl/fi/jl93h577gpogoh6i6r94d/Performance-report-data-random-1-just-provider-C.xlsm?dl=0&amp;rlkey=h1useg09vho9xgle0f9llkeid&nbsp;Power Bi&nbsp;https://www.dropbox.com/s/tpde5yduzs3drbs/Performance%20report%20Data%20Random.pbix?dl=0

anonymous · Answer

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)

Forum Discussion

Calculation Group format string expression mixing numbers and percentages help

4 Replies

Related content

Calculate Percentage

Calculating Percentages

Calculate percentage

percentage calculation

Percentage Calculation

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"

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%