cancel
Showing results for
Did you mean:
Microsoft

## 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 is correct, but the total is just sum of both divded by two, why is that?

this is the measure:

WeeklySumAVGPerWeek =

`WeeklySumAVGPerWeek = sumx(AggregatedWeeklyNewCoverageAllExpsView,DIVIDE(AggregatedWeeklyNewCoverageAllExpsView[WeeklySumPre],DISTINCTCOUNT(AggregatedWeeklyNewCoverageAllExpsView[TargetDate])))`

thanks!

11 REPLIES 11
Microsoft

Hi @MP_123,

Just keen to check.  What number are you expecting for the total? 🙂

Proud to be a Datanaut!

Microsoft

@Phil_Seamark-5,473,499.5

the sum of the sumx

5,316,278+157,221.50

thanks

Microsoft

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

Microsoft

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,

Proud to be a Datanaut!

Microsoft

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

Microsoft

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

Proud to be a Datanaut!

Microsoft

thank you very much @Phil_Seamark!

new table is a bit complicated because there is a lot of complex calculations with many relationships between tables

just wanted to know whether i'm doing something wrong and how the sumx calculation works

thanks a lot again

Microsoft

Here is a good article by @MattAllington that covers a bit about how SUMX works

http://exceleratorbi.com.au/sum-vs-sumx-in-dax/

there is a section at the bottom that looks similar to what you are trying to do, but I think you are actually after something differenct which is why I suggested the Calculated table approach

Proud to be a Datanaut!

Microsoft

thank you @Phil_Seamark

do you know how can i get the number of weeks - (not distinct)

1 for the first inference, 2 for the second - i want to get 3

count is not the answer because there is a lot of rows per targetdate, it's just aggregated in my table example

(if i will add more columns to the table, you will see the count of targetdate is about 4000)

Microsoft

Hi @MP_123

If you are using my Calculated Table approach, then I think the total should be easy, as it will just be the SUM of the "Count of Target Date" column from the new table.

Which should give you 1 for the first inference, 2 for the 2nd and a total of 3

Proud to be a Datanaut!

Microsoft

yes but i need to add to this table more columns that affects filtering and calculations, so the count of targetdate problem will be the same

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors