Help Clustered Bar Chart to show % Complete based on Filters

[Rdata]

I think I have just been staring at this for too long and have worked myself into a corner. 

So let's say I have the following data:

I have locations that get monthly utility usage numbers. I want to create a clustered bar chart that shows how many months have data.

The "Merge_Use" column can have numbers, blanks, and N/A. Any number OR N/A is considered complete. A blank or null is incomplete. 

I want a clustered bar chart that shows % complete, and is split by quarter and metric type, that shows the global total % complete, but can be filtered to show the % complete by region or individual location (relationships for TRT_ID to region is housed in a separate table). For some reason I can't wrap my mind around the measure that would do that. 

This was my first try. I used a calculated column, but it wasn't until after I got to the visual stage that I realized that my calculated column is static and won't be affected by filtering. (It sounds silly now but I made a column that assigned each completed field a % out of the total fields, i.e. 1/total # rows, thinking I could just sum these together in the visual). 

How would -you- do this? 

EDIT: Raw data

TRT_ID	Month	Year	Metric_Type	Merged_Use	Completed
19	January	2022	Electricity	8566	Yes
19	February	2022	Electricity	8052	Yes
19	March	2022	Electricity	9334	Yes
19	January	2022	Natural Gas	166.719	Yes
19	February	2022	Natural Gas	138.159	Yes
19	March	2022	Natural Gas	338.436	Yes
19	January	2022	Water		No
19	February	2022	Water		No
19	March	2022	Water		No
20	January	2022	Electricity		No
20	February	2022	Electricity		No
20	March	2022	Electricity		No
20	January	2022	Natural Gas		No
20	February	2022	Natural Gas		No
20	March	2022	Natural Gas		No
20	January	2022	Water		No
20	February	2022	Water		No
20	March	2022	Water		No
22	January	2022	Electricity	7405.86	Yes
22	February	2022	Electricity	7166.25	Yes
22	March	2022	Electricity	12397.47	Yes
22	January	2022	Natural Gas		No
22	February	2022	Natural Gas		No
22	March	2022	Natural Gas		No
22	January	2022	Water		No
22	February	2022	Water		No
22	March	2022	Water		No
24	January	2022	Electricity	3935	Yes
24	February	2022	Electricity	4557	Yes
24	March	2022	Electricity	4121	Yes
24	January	2022	Natural Gas		No
24	February	2022	Natural Gas		No
24	March	2022	Natural Gas		No
24	January	2022	Water		No
24	February	2022	Water		No
24	March	2022	Water		No
26	January	2022	Electricity	5817	Yes
26	February	2022	Electricity	5583	Yes
26	March	2022	Electricity	6083	Yes
26	January	2022	Natural Gas		No
26	February	2022	Natural Gas		No
26	March	2022	Natural Gas		No
26	January	2022	Water		No
26	February	2022	Water		No
26	March	2022	Water		No
27	January	2022	Electricity	6791	Yes
27	February	2022	Electricity	9008	Yes
27	March	2022	Electricity	7221	Yes
27	January	2022	Natural Gas		No
27	February	2022	Natural Gas		No
27	March	2022	Natural Gas		No
27	January	2022	Water		No
27	February	2022	Water		No
27	March	2022	Water		No
28	January	2022	Electricity		No
28	February	2022	Electricity		No
28	March	2022	Electricity		No
28	January	2022	Natural Gas		No
28	February	2022	Natural Gas		No
28	March	2022	Natural Gas		No
28	January	2022	Water		No
28	February	2022	Water		No
28	March	2022	Water		No
30	January	2022	Electricity	3886	Yes
30	February	2022	Electricity	4234	Yes
30	March	2022	Electricity	4609	Yes
30	January	2022	Natural Gas	N/A	Yes
30	February	2022	Natural Gas	N/A	Yes
30	March	2022	Natural Gas	N/A	Yes
30	January	2022	Water		No
30	February	2022	Water		No
30	March	2022	Water		No
34	January	2022	Electricity	8534	Yes
34	February	2022	Electricity	7708	Yes
34	March	2022	Electricity	6962	Yes
34	January	2022	Natural Gas		No
34	February	2022	Natural Gas		No
34	March	2022	Natural Gas		No
34	January	2022	Water	2.041	Yes
34	February	2022	Water	1.843	Yes
34	March	2022	Water	0	Yes

Location region table:

TRT_ID	Region
19	APAC
20	APAC
22	NA
24	NA
26	EMEA
27	EMEA
28	EMEA
30	APAC
34	NA

[Rdata · Accepted solution]

I may have solved my own problem. 

I added a conditional column with Yes/No for completed based off my criteria. (i.e. if  "" then "No", else "Yes")

Added the following measure: 

% YES =

DIVIDE (

    CALCULATE ( COUNT ( Leadership_Usage_Tracking_v2[Completed] ), Leadership_Usage_Tracking_v2[Completed] = "YES" ),

    CALCULATE ( COUNT ( Leadership_Usage_Tracking_v2[Completed] ), ALLSELECTED ( Leadership_Usage_Tracking_v2[Completed] ) )

)

Seeing if this works. 

[PaulDBrown]

That's the method I was thinking: create a new conditional column in Power Query to define "Complete" vs "Incomplete" and the use the column to filter the visual with a COUNT measure over CALCULATE([COUNT Measure], ALLSELECTED())

[Rdata]

It seems to be working the way I want it to so far! Now onto my next problem, haha. Thank you for your time! 

[PaulDBrown]

Please share some sample, non-confidential data or a link to a PBIX file and a depiction of the expected output.

please include rows where Merge_Type is either a number, N/A, blank or 0 (as per your brief) to be able to provide a working solution.

[Rdata]

I provided a screenshot of the data table and the expected visual; should it be in a different format? 

[PaulDBrown]

Please post it as actual data (not an image) so we can copy and paste it into a PBIX file (and please include rows with Blank and N/A)

[Rdata]

Done! I added it to the original post. Please let me know if it is enough.