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])))
Just keen to check. What number are you expecting for the total?
Proud to be a Datanaut!
the sum of the sumx
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(
"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)
Engage and empower students with Power BI!
Continue your learning in our online communities.
Travel to Melbourne and network with thousands of peers!
Check out what's new in the Power BI Community!