- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

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

Showing results for

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Weighted Average on Narrow Table Using DAX Measure

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Weighted Average on Narrow Table Using DAX Measure

05-17-2024
10:31 AM

My Fact Table is quite narrow with a few Dimension tables (think EAV model if you are familiar with dB architecture). Let's say I have the following 'Fact' table:

EntryID | AttrID | Value |

1 | 1 | 5 |

2 | 1 | 10 |

1 | 2 | 15 |

2 | 2 | 20 |

and the following 'Dim' table with a one-to-many join on [AttriID] in the Data Model:

AttrID | Category |

1 | A |

2 | B |

For the most part, my measures rely on:

`CALCULATE(SUM('Fact'[Value]), ALL('Dim'), 'Dim'[Category] = "A")`

which correctly returns 15. This works fine when I just need a simple aggregation like SUM, AVERAGE, etc. for a given Category.

For a handful of my measures, I have to wheel out the DIVIDE function to get to the proper aggregated value. This is simple if all I am doing is a SUM(A) / SUM(B) e.g.

`DIVIDE(CALCULATE(SUM('Fact'[Value]), ALL('Dim'), 'Dim'[Category] = "A"), CALCULATE(SUM('Fact'[Value]), ALL('Dim'), 'Dim'[Category] = "B"))`

which correctly returns 15 / 35 or 0.4285....

But, sometimes I need SUM(A(i)*B(i)) / SUM(B) where i is an index across all EntryIDs, but I can't get it to work. To be precise, I need (5*15 + 10*20) / (15 + 20) which equals 7.857...

Part of the complication arises comes from joining up the proper values by EntryID versus (5*20 + 10*15) or some other jumbled order when doing this across millions of rows.

I have researched this for hours with no solutions. And, for design reasons, I need to do this in a measure that is a single DAX line that does not rely on defining VARs, etc.

Please help and thank you in advance!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-19-2024
07:51 PM

Hi @WishAskedSooner ,

First of all, many thanks to @lbendlin for your very quick and effective replies.

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new measure to calculate sum across all entry id.

```
Weighted Average =
DIVIDE(
SUMX(
FILTER(
'Fact Table',
RELATED('Dim Table'[Category]) = "A"
),
'Fact Table'[Value] * LOOKUPVALUE('Fact Table'[Value], 'Fact Table'[EntryID],'Fact Table'[EntryID], 'Dim Table'[Category], "B")
),
CALCULATE(SUM('Fact Table'[Value]), ALL('Dim Table'), 'Dim Table'[Category] = "B")
)
```

3.Select the measure and edit the number of shown for the value.

4.Drag the measure into the card visual. The result is shown below.

You can also view the following links to learn about DAX function.

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

SUMX function (DAX) - DAX | Microsoft Learn

Best Regards,

Wisdom Wu

If this post ** helps**, then please consider

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-19-2024
07:51 PM

Hi @WishAskedSooner ,

First of all, many thanks to @lbendlin for your very quick and effective replies.

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new measure to calculate sum across all entry id.

```
Weighted Average =
DIVIDE(
SUMX(
FILTER(
'Fact Table',
RELATED('Dim Table'[Category]) = "A"
),
'Fact Table'[Value] * LOOKUPVALUE('Fact Table'[Value], 'Fact Table'[EntryID],'Fact Table'[EntryID], 'Dim Table'[Category], "B")
),
CALCULATE(SUM('Fact Table'[Value]), ALL('Dim Table'), 'Dim Table'[Category] = "B")
)
```

3.Select the measure and edit the number of shown for the value.

4.Drag the measure into the card visual. The result is shown below.

You can also view the following links to learn about DAX function.

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

SUMX function (DAX) - DAX | Microsoft Learn

Best Regards,

Wisdom Wu

If this post ** helps**, then please consider

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-20-2024
09:26 PM

Simply, simply Brilliant!!!! I was able to implement this into my actual data model which is slightly more complex than the example I provided (a total of three columns that need to be included in the LOOKUP to filter my fact table properly) and it works beautifully! I can't even begin to describe how thankful I am for your help. I have been agonizing over this for days. Thank you!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-17-2024
01:33 PM

Here is hopefully more meaningful data that fully describes my problem. I have three tables:

**Fact**

**DimE** joined on EID

**DimA** joined on AID

and the following Data Model:

What I am trying to do is multiply Category A with Category B (not Category C) for EID X. Repeat for EID Y. Then sum the individual results. I have created a Pivot to illustrate:

The final result would be 75 + 200 = 275. In other words, I am trying to do the following:

(5*15 + 10*20) = 275

I am hoping I can do this in a DAX measure versus having to actually pivot the Fact table to a new table.

I hope this makes more sense. Thanks in advance for the help!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-17-2024
12:58 PM

It was suggested I provide sample data that fully covers my issue. So I will try to do that. I have the following three tables:

**Fact**

**DimE** joined on EID

**DimA** joined on AID

The following Data Model

I am trying to find a way to multiply the values for Category A and Category B (not Category C) in the Fact table by EID then sum the individual results.

For example, for EID 1, I want to multiply AID 1 and AID 2 together because they both belong to EID 1. Furthermore, I want to be able to specify this using the joined Category in DimA, i.e. for EID 1 multiply Category A by Category B. Then, for EID 2, multiply Category A and Category B. Repeat for each EID in the Fact table. Then sum the individual results. So,

(5*15 + 10*20) = 275

I would provide examples of what I have tried, but it is pointless because none come even close. I literally have no idea how to do this. Please help.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-17-2024
11:23 AM

Thank you for your reply. You seem to imply that I could use either SUMX or PRODUCTX to get to the correct value which is interesting.

However, I am very new to PowerBI and DAX, so if you could provide the syntax with how to use those functions with or without CALCULATE, that would be a huge help. As I mentioned above, I have researched this for at least four hours without success, and this includes experimenting with SUMX and PRODUCTX.

I am not sure what you mean by providing sample data. I can't upload my actual PBI model due to security reasons. I suppose I could create a PBI project with the data that I included above, but is that extra effort really necessary?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-17-2024
12:43 PM

It's totally your choice. I can only assist with meaningful sample data.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-17-2024
11:12 AM

use aggregation functions like SUMX or PRODUCTX.

Please provide sample data **that fully covers your issue**.

Please show the expected outcome based on the sample data you provided.

Announcements

Check out the September 2024 Power BI update to learn about new features.

Learn from experts, get hands-on experience, and win awesome prizes.

Featured Topics

Top Solution Authors

User | Count |
---|---|

114 | |

105 | |

96 | |

38 | |

30 |