Search code examples
powerbipowerbi-desktopdashboard

Clustered Bar Chart to show % Complete based on Filters

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:Data set

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 > 0 OR N/A is considered complete. 0 or blank 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).

Attempted visual

How would you do this?

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")

    Then 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] ) )
)

    Seems to be working so far!