Forum Discussion

Forrestgump's avatar
Forrestgump
Frequent Visitor
8 years ago
Solved

create a bar chart with an average line running through

Hi All, I am trying to create a bar chart with an average line running through. The matrix button works as below:- [CODE] Function % Complete Function 1 54.50% Function 2 40.00% ...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

The average in Excel is 12.8%. The one displayed in Power BI is .13 (same if you increase decimal count). I'm not understanding how you are getting your average as your method seems to disagree with Excel. Apparently your concept of average is different than the standard way of conceptualizing average. What you are asking for is the overall % complete, not an average of % complete. You would do that by creating a measure that essentially does this:

 

Measure = COUNTX(FILTER(ALL('Table'),[Column]="Complete"),[Column]) / COUNTX(ALL('Table'),[Column])

 

Difficult to say exactly without source data, which is what I thought you provided but apparently not. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Forrestgump's avatar
Forrestgump
Frequent Visitor
8 years ago

hi Greg,

 

Thanks for your reply. So i am looking for the 10.8% which comes form the overall calculation:-

 

SPU / Function# CompleteEligible EmployeesPercentage
Function 1183354.5%
Function 2143540.0%
Function 3185234.6%
Function 41333.3%
Function 56025923.2%
Function 67233621.4%
Function 71520.0%
Function 85127518.5%
Function 984517.8%
Function 109553517.8%
Function 1174017.5%
Function 124124716.6%
Function 13106714.9%
Function 146648913.5%
Function 151813613.2%
Function 161814112.8%
Function 17119311.8%
Function 181110810.2%
Function 193029810.1%
Function 20141499.4%
Function 21283138.9%
Function 223348.8%
Function 23303748.0%
Function 24344287.9%
Function 256896.7%
Function 26588676.7%
Function 27122075.8%
Function 28295735.1%
Function 2961913.1%
Function 3041372.9%
Function 311382.6%
Function 3251902.6%
Function 332772.6%
Function 3473681.9%
Function 35050.0%
Function 360170.0%
Function 370110.0%
Function 38070.0%
Total789727210.8%
    • Forrestgump's avatar
      Forrestgump
      Frequent Visitor
      8 years ago

      Thanks, Frank, Greg for all your help! 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

     

    Measure = SUM('Table'[# Complete]) / SUM('Table'[Eligible Employees])
    • Forrestgump's avatar
      Forrestgump
      Frequent Visitor
      8 years ago

      The issue with this is that [#Complete] and [Eligible Employees] are measures not columns so this won't work i believe.

       

      # Complete = DistinctCount(SurveyData[ID])

      Eligible Employees = CountRows(HeadcountData)

      • v-frfei-msft's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        Hi Forrestgump,

         

        Here I made one sample for your reference.

         

        1. Create the measures as you offered. 

         

        # Complete = DistinctCount(SurveyData[ID])
        Eligible Employees = CountRows(HeadcountData)

        2. Then create a measure using the formula and create a Line and stacked column chart to work around.

         

        Measure = CALCULATE(DISTINCTCOUNT(SurveyData[id]),ALL(SurveyData))/CALCULATE(COUNTROWS(HeadcountData),ALL(SurveyData))

         

        For more details, please check the pbix as attached.

         

        https://www.dropbox.com/s/g56davhns5i6kt1/create%20a%20bar%20chart.pbix?dl=0

         

        Regards,

        Frank