SUMX calculation in the right context

[Anonymous]

Let me first explain the data structure. There is one table called Query with the following columns: 

"Ticker" (type=text) contains ticker names

"Bucket" (type=text) contains time buckets 00-01, 01-03, etc.

"ID" (type=text) contains a unique text string

"Score" (type=decimal number) contains decimal numbers

"Value" (type=decimal number) contains decimal numbers

A Ticker has several IDs. Every ID lies in a Bucket. Every ID has a Score and a Value.

In the following matrix visual I have represented several custom defined measures. The matrix has two levels (rows) which are expanded in the picture. The first level is Ticker and the second level is Bucket.

Weighted Average represents the Value weighted average Score of a Ticker overall or in a specific Bucket.

Weighted Market Average represents the Value weighted average Score of all Tickers overall or in a specific Bucket.

Weights w.r.t Ticker represents the Value weight of a Bucket with respect to a Ticker.

The measures are defined as follows:

Weighted Average = DIVIDE(SUMX(Query; Query[Score]*Query[Value]); SUM(Query[Value]))

Weighted Market Average = CALCULATE(DIVIDE(SUMX(Query; Query[Score]*Query[Value]); SUM(Query[Value])); ALL(Query[Ticker]))

Weights w.r.t Ticker = DIVIDE(SUM(Query[Value]); CALCULATE(SUM(Query[Value]); ALL(Query[Bucket])))

Bucket Weighted Ticker Average = SUMX(ALL(Query[Bucket]); Query[Weights w.r.t Ticker]*Query[Weighted Average])

Bucket Weighted Market Average = SUMX(ALL(Query[Bucket]); Query[Weights w.r.t Ticker]*Query[Weighted Market Average])

Currently, the measures "Bucket Weighted Ticker Average" and "Bucket Weighted Market Average" are not the way I would like them to be. 

On the Ticker level I want the measure "Bucket Weighted Market Average" to calculate the sum of the product of "Weights w.r.t Ticker" and "Weighted Market Average" over all available Buckets for a Ticker.

And on the Bucket level inside a Ticker I want the measure "Bucket Weighted Market Average" to calculate the sum of the product of "Weights w.r.t Ticker" and "Weighted Market Average" over the specific Bucket in the current context, hence the sum is only over one element and just the product.

I am aware that I am currently using ALL(Query[Bucket]) in the SUMX function such that I get the right value on the Ticker level but also the same values on the Bucket level.

How do I define the measure "Bucket Weighted Market Average" correctly in order to achieve my goal? How do I use SUMX? Over what table do I have to use SUMX?

The measure "Bucket Weighted Ticker Average" is analogous to "Bucket Weighted Market Average" where "Weighted Market Average" is replaced by "Weighted Average" in the measure definition.

My end goal is to represent the ratio (Bucket Weighted Ticker Average)/(Bucket Weighted Market Average) in an additional column.

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

It seems you may create a measure with ISFILTERED Function to change the total value for each dimension.You may have a look at below articles.

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

https://xxlbi.com/blog/new-dax-function-isinscope/

Regards,

Cherie

[Anonymous]

Thanks a lot! I could solve my problem with HASONEFILTER.