1. I imported your file to Power Query Editor.
2. In the editor
Power Query:
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
Excel Formula:
=CALCULATE(SUMX(Table1,Table1[Count of Closed]+Table1[Count of Open]))
6. In Power Pivot I created the following measure for percentage
Excel Formula:
=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 | | | | | | | |
---|
|
---|