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.

 

[lrobbo314]

Sorry for all the confusion, but THANK YOU SCOTTSEN. After playing around with your formula, I finally got what I was looking for. Just, in case anyone else ever needs this crazy formula the final product was

x:=CALCULATE(sumx(DISTINCT(tbl_xTraffic[Entry Zone]),sqrt([Entries])),ALLSELECTED(tbl_xTraffic))

Thanks again to everyone.

 

[Kazlik]

This should work now, I am still learning Power Pivot as well. I made a sample file with calculated colummns to get the SQRT of the product count. I then made the two measure below so i can get a distinct sum.



CalculatedColumn1
=CALCULATE(SUM([count]),FILTER(Sheet1,Sheet1[product] = EARLIER(Sheet1[product])))
SQRT Value
=SQRT(Sheet1[CalculatedColumn1])

MaxSQRT:=MAX(Sheet1[SQRT Value])
total:=SUMX(DISTINCT(Sheet1[product]),[MaxSQRT])

 

[scottsen]

Nah, pretty sure I am just confused on what you have on your rows (you say you measure, but that isn't even possible). Can you share a sample workbook on onedrive/googledrive/dropbox?

 

[lrobbo314]

Hey guys, not sure if you saw the post a few back, but with your help, especially Scottsen, I got it figured out. The formula that I ended up with is CALCULATE(sumx(DISTINCT(tbl_xTraffic[Entry Zone]),sqrt([Entries])),ALLSELECTED(tbl_xTraffic)).

The Rows of my pivot table is tbl_xTraffic[Entry Zone].

[Entries] is the measure. [Entries] is tbl_xTraffic[APPs] + tbl_xTraffic[GTAs]

Hopefully I've explained it well.

But, either way, a MILLION thanks!

This was a pain to figure out.