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 = sumx(AggregatedWeeklyNewCoverageAllExpsView,DIVIDE(AggregatedWeeklyNewCoverageAllExpsView[WeeklySumPre],DISTINCTCOUNT(AggregatedWeeklyNewCoverageAllExpsView[TargetDate])))
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
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.
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])) )
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
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
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)
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
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
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Mark your calendars and join us for our next Power BI Dev Camp!