Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help 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:

 

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_IDMonthYearMetric_TypeMerged_UseCompleted
19January2022Electricity8566Yes
19February2022Electricity8052Yes
19March2022Electricity9334Yes
19January2022Natural Gas166.719Yes
19February2022Natural Gas138.159Yes
19March2022Natural Gas338.436Yes
19January2022Water No
19February2022Water No
19March2022Water No
20January2022Electricity No
20February2022Electricity No
20March2022Electricity No
20January2022Natural Gas No
20February2022Natural Gas No
20March2022Natural Gas No
20January2022Water No
20February2022Water No
20March2022Water No
22January2022Electricity7405.86Yes
22February2022Electricity7166.25Yes
22March2022Electricity12397.47Yes
22January2022Natural Gas No
22February2022Natural Gas No
22March2022Natural Gas No
22January2022Water No
22February2022Water No
22March2022Water No
24January2022Electricity3935Yes
24February2022Electricity4557Yes
24March2022Electricity4121Yes
24January2022Natural Gas No
24February2022Natural Gas No
24March2022Natural Gas No
24January2022Water No
24February2022Water No
24March2022Water No
26January2022Electricity5817Yes
26February2022Electricity5583Yes
26March2022Electricity6083Yes
26January2022Natural Gas No
26February2022Natural Gas No
26March2022Natural Gas No
26January2022Water No
26February2022Water No
26March2022Water No
27January2022Electricity6791Yes
27February2022Electricity9008Yes
27March2022Electricity7221Yes
27January2022Natural Gas No
27February2022Natural Gas No
27March2022Natural Gas No
27January2022Water No
27February2022Water No
27March2022Water No
28January2022Electricity No
28February2022Electricity No
28March2022Electricity No
28January2022Natural Gas No
28February2022Natural Gas No
28March2022Natural Gas No
28January2022Water No
28February2022Water No
28March2022Water No
30January2022Electricity3886Yes
30February2022Electricity4234Yes
30March2022Electricity4609Yes
30January2022Natural GasN/AYes
30February2022Natural GasN/AYes
30March2022Natural GasN/AYes
30January2022Water No
30February2022Water No
30March2022Water No
34January2022Electricity8534Yes
34February2022Electricity7708Yes
34March2022Electricity6962Yes
34January2022Natural Gas No
34February2022Natural Gas No
34March2022Natural Gas No
34January2022Water2.041Yes
34February2022Water1.843Yes
34March2022Water0Yes

 

Location region table:

 

TRT_IDRegion
19APAC
20APAC
22NA
24NA
26EMEA
27EMEA
28EMEA
30APAC
34NA
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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. 

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      4 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

      • PaulDBrown's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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)