cancel
Showing results for
Did you mean:
Highlighted Anonymous
Not applicable

## SUMX calculation in the right context

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.
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Microsoft

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Highlighted Microsoft

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted Anonymous
Not applicable

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