Forum Discussion

MP_123's avatar
MP_123
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

sumx question

hi, i want to create a weighted average as below image the most left is the numirator , and the most right is the denominator. i want to create: 5316278/1+314443/2 the result for each row i...
Need Help
MP_123's avatar
MP_123
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Phil_Seamark-5,473,499.5

the sum of the sumx

5,316,278+157,221.50

 

thanks

MP_123's avatar
MP_123
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

this is the definition of sumx -

Returns the sum of an expression evaluated for each row in a table.

 

seems like it's not the sum of an expression-

what the PBI does is sum(UsersCount)\2

 

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi there,

     

    Any chance you can post the sample set of data that makes this up.  As well as the code you are using to build your [Target] measure.

     

    Cheers,

    • MP_123's avatar
      MP_123
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      thank you Phil_Seamark!

      this is my data

      what I want to get is avg per week, so for 'User Location_Main' - it will be (158612+155831)\2 (=distinct count of target date)

      and for the first, it will be just divided by 1.

      what I want to get is the sum of this two averages. power BI divided the total sum by 2 and it's wrong

      • Phil_Seamark's avatar
        Phil_Seamark
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Would you consider a DAX calulated table as a solution?

         

        Something along these lines?  You can then simply SUM the [WeeklySumAVGPerWeek] column

         

         

        New Table = SUMMARIZECOLUMNS(
            'AggregatedWeeklyNewCoverageAllExpsView'[Inference Name],
            "Weekly Sum Pre" , SUM(AggregatedWeeklyNewCoverageAllExpsView[WeeklySumPre]),
            "Count of Target Date" , DISTINCTCOUNT(AggregatedWeeklyNewCoverageAllExpsView[TargetDate]),
            "WeeklySumAVGPerWeek" , DIVIDE(SUM(AggregatedWeeklyNewCoverageAllExpsView[WeeklySumPre]), DISTINCTCOUNT(AggregatedWeeklyNewCoverageAllExpsView[TargetDate]))
            )