cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MP_123 Member
Member

sumx question

hi,

i want to create a weighted average as below image

1.PNG

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
Phil_Seamark Super Contributor
Super Contributor

Re: sumx question

Hi @MP_123,

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

MP_123 Member
Member

Re: sumx question

@Phil_Seamark-5,473,499.5

the sum of the sumx

5,316,278+157,221.50

 

thanks

MP_123 Member
Member

Re: sumx question

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 Super Contributor
Super Contributor

Re: sumx question

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,


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

MP_123 Member
Member

Re: sumx question

Capture3.PNG

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 Super Contributor
Super Contributor

Re: sumx question

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

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

MP_123 Member
Member

Re: sumx question

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 Super Contributor
Super Contributor

Re: sumx question

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

MP_123 Member
Member

Re: sumx question

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)

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 209 members 1,557 guests
Please welcome our newest community members: