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

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
Microsoft
Microsoft

Hi @MP_123,

 

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


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

Proud to be a Datanaut!

@Phil_Seamark-5,473,499.5

the sum of the sumx

5,316,278+157,221.50

 

thanks

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

 

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!

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

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!

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

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!

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)

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


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

Proud to be a Datanaut!

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 Smiley Sad

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

Get Ready for Power BI Dev Camp

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