How to add percentage at the end of Pivot Table

[Manojlo]

Hello,

Is there any option to add calculated field in column "E" with formula to divide values from Grand Total column "D" with Closed column "B", in order to calculate Closed Rate =SUM(B/D)?
I inserted yellow column manually for example what I want to achieved.

 

[Manojlo]

Anyone have an idea?

 

[alansidman]

Bring your data into Power Pivot and build your Pivot Table there. Create a measure to divide the closed by the total. If you post a sample raw data using XL2BB instead of a picture of your PT, then maybe someone will do the calculation for you. Cannot manipulate data in a picture and you only provided the expected result and not the raw data.

 

[Manojlo]

Thank you, I have problem to use XL2BB, I try with picture...

 

[alansidman]

1. I imported your file to Power Query Editor.
2. In the editor

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FormatDate = Table.TransformColumnTypes(Source,{{"Reported M/Y", type date}}),
    ClosedCount = Table.AddColumn(FormatDate, "Closed", each if [#"Close/Open"] = "CLOSED" then 1 else null),
    OpenCount = Table.AddColumn(ClosedCount, "Open", each if [#"Close/Open"]= "OPEN" then 1 else null),
    RemovedColumn = Table.RemoveColumns(OpenCount,{"Close/Open"})
in
    RemovedColumn

3. Closed and Loaded to the Data Model (Power Pivot)
4. In Power Pivot I created Pivot that resembled your expected results before percentage
5. In Power Pivot I created the following measure for Totals

=CALCULATE(SUMX(Table1,Table1[Count of Closed]+Table1[Count of Open]))

6. In Power Pivot I created the following measure for percentage

=CALCULATE(DIVIDE([Count of Closed],Table1[Total]))

7. Formatted the percentage in the Pivot Table

Note: both measures are in DAX

PowerPivotPct (version 2).xlsb
	A	B	C	D	E	F	G	H	I	J	K
1	Title	Reported Incident	Reported M/Y	Close/Open			Row Labels	Count of Open	Count of Closed	Total	Pct Closed
2	Title 1	1	23-Nov	CLOSED			1/24/2024	1	3	4	75.00%
3	Title 2	1	24-Feb	CLOSED			2/24/2024		1	1	100.00%
4	Title 3	1	24-Jan	OPEN			11/23/2024		1	1	100.00%
5	Title 4	1	24-Jan	CLOSED			Grand Total	1	5	6	83.33%
6	Title 5	1	24-Jan	CLOSED
7	Title 6	1	24-Jan	CLOSED
Sheet1

 

[Manojlo]

1. I imported your file to Power Query Editor.
2. In the editor

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FormatDate = Table.TransformColumnTypes(Source,{{"Reported M/Y", type date}}),
    ClosedCount = Table.AddColumn(FormatDate, "Closed", each if [#"Close/Open"] = "CLOSED" then 1 else null),
    OpenCount = Table.AddColumn(ClosedCount, "Open", each if [#"Close/Open"]= "OPEN" then 1 else null),
    RemovedColumn = Table.RemoveColumns(OpenCount,{"Close/Open"})
in
    RemovedColumn

3. Closed and Loaded to the Data Model (Power Pivot)
4. In Power Pivot I created Pivot that resembled your expected results before percentage
5. In Power Pivot I created the following measure for Totals

=CALCULATE(SUMX(Table1,Table1[Count of Closed]+Table1[Count of Open]))

6. In Power Pivot I created the following measure for percentage

=CALCULATE(DIVIDE([Count of Closed],Table1[Total]))

7. Formatted the percentage in the Pivot Table

Note: both measures are in DAX

PowerPivotPct (version 2).xlsb
	A	B	C	D	E	F	G	H	I	J	K
1	Title	Reported Incident	Reported M/Y	Close/Open			Row Labels	Count of Open	Count of Closed	Total	Pct Closed
2	Title 1	1	23-Nov	CLOSED			1/24/2024	1	3	4	75.00%
3	Title 2	1	24-Feb	CLOSED			2/24/2024		1	1	100.00%
4	Title 3	1	24-Jan	OPEN			11/23/2024		1	1	100.00%
5	Title 4	1	24-Jan	CLOSED			Grand Total	1	5	6	83.33%
6	Title 5	1	24-Jan	CLOSED
7	Title 6	1	24-Jan	CLOSED
Sheet1

Thank you very much!
Could you upload a file here?