- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- SUMX calculation in the right context

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

leumvs

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-05-2019
12:07 PM

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.

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

v-cherch-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-05-2019
10:22 PM

Hi @leumvs

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.

If this post

2 REPLIES 2

v-cherch-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-05-2019
10:22 PM

Hi @leumvs

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.

If this post

leumvs

Frequent Visitor

Re: SUMX calculation in the right context

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-11-2019
05:33 AM

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