Sumx?

[lrobbo314]

Sorry if this is a cross post, but I think I might get a response in here since it is PowerPivot related.



I have a spreadsheet formula that looks like
Spreadsheet:

=(SQRT(E4)/SUMPRODUCT(SQRT($E$4:$E$13)))*Charts!$A$2

This works on the spreadsheet because I can do an array calulation with sumproduct.

SUMPRODUCT(SQRT($E$4:$E$13))

evaluates to 41.55453251.
But, no matter what I do with SumX or any other combination of measure calculations, the best I can get is 13.52774926. Which, in the spreadsheet is the same result I get when I enter

=SQRT(SUM(E4:E13))

Currently, the measure I have that is getting me 13.52774926 is the following

t:=CALCULATE(sqrt([Entries]),ALLSELECTED(tbl_xTraffic))

Where [Entries] = Column x + Column y
Like I said, I have tried SumX and a lot of other ways to try to get the 41.55453251 result, but I can't figure it out.
You would save me a lot of banging my head against the wall with any help.
Thanks.

 

[scottsen]

Can you paste a bit of sample data?

I expect you will need like... SQRT(SUMX(Table1, Table1[Field1]*Table2[Field2])) ... but would help to see it.

 

[lrobbo314]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Row Labels
[/TD]
[TD]t
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]5.8309
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]4.3588
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]3.8729
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1.7305
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]4.2426
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]3.6055
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]4.7958
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]3.8729
[/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD]13.5277
[/TD]
[/TR]
</tbody>[/TABLE]

 

[lrobbo314]

I have 2 columns of Apps & GTA. Both APPs & GTA just have numbers in them. I have a measure that combines them which is called [Entries].
When I put this into my pivot table, they are separated out by zones. So in the pivot table, I have [Zone] in the Rows, and my other measures in the VALUES.
t:=SQRT([Entries]) gives me (The table above)

but the grand total is wrong on the pivot table. Those values actually add up to 41.55453251, which is the value I am trying to get by writing this measure.
Let me know if you need more info, and thanks for the help.

 

[lrobbo314]

OK, so I guess SUMX is out.

SUMX takes the square root of every [Entries] and adds them together. This is wrong. For example, Zone 1 has 34 entries and the square root of 34 is 5.83 like I have in my table above. The SUMX, summing the square root of every line gives me 20.03 for Zone 1.

Like I said before, the numbers that the measure SQRT([Entries]) gives me is correct for each Zone. But, the total is wrong. It adds up to 41.55, but the Grand Total shows 13.52.
13.52 = the worksheet formula SQRT(SUM(E4:E13))
41.55 (The number I need to produce with a measure = the worksheet formula =SUMPRODUCT(SQRT(E4:E13))

This is driving me crazy.

 

[Kazlik]

I think the grand total of a measure column is the formula on the values in the column and not the sum of the values in column. You might try something like this where your measure has SUM in it.

Calculated column – Traffic Total
CALCULATE(sqrt([Entries]), FILTER(Sheet1,Sheet1[tbl_xTraffic] = EARLIER (Sheet1[tbl_xTraffic])))

Measure
Total:=SUM([Traffic total])

 

[lrobbo314]

I have found that you are correct. Instead of giving me the sum of each row, like I would expect, it is giving me the SQRT of the sum of all the Entries.
The total entries = 183 and SQRT(183) = 13.52.

But, I don't understand what the Sheet1 reference in your formula above means. What is that referencing? Not the worksheet in Excel itself right.

Thanks for looking at this.

 

[scottsen]

The grand total cell is just the evaluation of the measure... without the filter (from whatever you have on rows) applied.

But you can (basically) force that behavior. =SUMX(DISTINCT(Table1[WhateverIsOnRows]), [WhateverMeasure])

 

[lrobbo314]

To clarify. My table is called tbl_xTraffic. That table has columns APP and GTA. [Entries] is a measure that just adds APPs + GTAs. I added a calculated column called Entries2 so I could try your formula. I did this "


=CALCULATE(SQRT([Entries2]),FILTER(tbl_xTraffic,tbl_xTraffic[Entries2] = EARLIER(tbl_xTraffic[Entries2])))


" but it gave m #ERROR saying Entries2 cannot be used in the expression.

 

[lrobbo314]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Your Formula
[/TD]
[TD]Desired
[/TD]
[/TR]
[TR]
[TD]12.59
[/TD]
[TD]5.83
[/TD]
[/TR]
[TR]
[TD]8.41
[/TD]
[TD]4.35
[/TD]
[/TR]
[TR]
[TD]6.8
[/TD]
[TD]3.87
[/TD]
[/TR]
[TR]
[TD]1.73
[/TD]
[TD]1.73
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]8.06
[/TD]
[TD]4.24
[/TD]
[/TR]
[TR]
[TD]6.18
[/TD]
[TD]3.60
[/TD]
[/TR]
[TR]
[TD]8.22
[/TD]
[TD]4.79
[/TD]
[/TR]
[TR]
[TD]6.82
[/TD]
[TD]4.24
[/TD]
[/TR]
[TR]
[TD]6.27
[/TD]
[TD]3.87
[/TD]
[/TR]
[TR]
[TD]33.99
[/TD]
[TD]41.55
[/TD]
[/TR]
</tbody>[/TABLE]
SCOTTSEN,

I tried your formula, but it didn't really work.

=SUMX(DISTINCT(Table1[WhateverIsOnRows]), [WhateverMeasure])
[WhateverIsOnRows] is my measure. The measure is Column APP + Column GTA. The measure is called Entries.

But, to try to make your formula work, I made a calculated column called ENTR, which is the same thing, APPs + GTAs.
The formula looked like sumx(DISTINCT(tbl_xTraffic[entr]),sqrt([Entries]))

The table above shows what the formula gave me and what the results should be, with the bottom row being the Grand Total.

Also, that formula's Grand Total doesn't add up correctly either.

I must be putting it in wrong.