sumx question

[MP_123]

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!

[Phil_Seamark]

Hi @MP_123,

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

[MP_123]

@Phil_Seamark-5,473,499.5

the sum of the sumx

5,316,278+157,221.50

thanks

[MP_123]

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]

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]

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]

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

[MP_123]

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

[Phil_Seamark]

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

[MP_123]

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)

[Phil_Seamark]

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

[MP_123]

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