Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
mwjansen
Helper I
Helper I

Multiply averages in two tables with filters applied

‎02-26-2025 10:40 AM

Hello. 

1 month in on Power BI.

 

I have two tables, both with numeric columns. Average two columns and multiply the results.

SS = Round(Average('Table1'[ColA]),2) * Round(Average('Table2'[ColB], 2)

 

This works just fine.
Both tables have a Year column, and they are related by a third table with just a Year column. 
 
When I apply the Year filter, the product is way off. 
 
Help? 
 
Forgot to include snippets of tables: 
YearColA YearColB YEAR
2025100 20252 2020
2025100 20254 2021
2025100 20252 2022
2025100 20252 2023
2024100 20252 2024
202499 20254 2025
2024100 20252 2026
2024100 20252 2027
202499 20253 2028
2024100 20254  
202499 20252  
2024100 20252  
2024100 20252  
2024100 20252  

 

                                   
Labels:
Message 1 of 7
394 Views
0
1 ACCEPTED SOLUTION
Irwan
Super User
Super User
In response to mwjansen

‎02-27-2025 03:40 PM

hello @mwjansen 

 

i think you need to make another DIM table for Division.

Here i made a Division Dim table from SUMMARIZE as an example (i think you have a list of Division that can be worked as your Dim).

Then, you create a relationship between Division and those two Fact table.

Irwan_0-1740699432926.png

after that, you can do the measure as yours.
Take slicer value from Year dim table and Division dim table.

Irwan_1-1740699480956.png

Irwan_2-1740699492188.png

 

as the result above, in 2025

Mission Critical has 100 average value in COLA and 2.5 average value in COLB

Operations has 100 average value in COLA and 3 average value in COLB.

 

Hope this will help.

Thank you.

View solution in original post

mwJansen - 28Feb25.pbix
Message 4 of 7
305 Views
0
6 REPLIES 6
Anonymous
Not applicable

‎02-27-2025 10:22 PM

Hi, @mwjansen 

I wish you all the best. Previously Super user have provided a solution to help you solve the problem. Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

 

 

Best Regards

Jianpeng Li

Message 7 of 7
300 Views
0
mwjansen
Helper I
Helper I

‎02-27-2025 12:40 PM

Thank you Irwan. You are correct, it does work fine. Dumb me forgot where it failed. In the visuals (clustered column chart), the x-axis is 'Division' (found in both Table1 and Table2), and the y-axis is Table1 Average of ColA (and another visual where y-axis is Table2 Average of ColB). 

 

It is here where the calculations are off for each Division. 

 

DivisionYearCOLA YearDIVISIONCOLB YEAR
Mission Critical2025100 2025Mission Critical4 2020
Mission Critical2025100 2025Operations2 2021
Operations2025100 2025Commercial/Special Projects4 2022
Mission Critical2025100 2025Mission Critical2 2023
Mission Critical2024100 2025Mission Critical2 2024
Mission Critical202499 2025Mission Critical2 2025
Mission Critical2024100 2025Operations4 2026
Mission Critical2024100 2025Mission Critical2 2027
Mission Critical202499 2025Mission Critical2 2028
Mission Critical2024100 2025Mission Critical3  
Mission Critical202499 2025Mission Critical4  
Mission Critical2024100 2025Mission Critical2  
Mission Critical2024100 2025Mission Critical2  
Message 3 of 7
310 Views
0
Irwan
Super User
Super User
In response to mwjansen

‎02-27-2025 03:40 PM

hello @mwjansen 

 

i think you need to make another DIM table for Division.

Here i made a Division Dim table from SUMMARIZE as an example (i think you have a list of Division that can be worked as your Dim).

Then, you create a relationship between Division and those two Fact table.

Irwan_0-1740699432926.png

after that, you can do the measure as yours.
Take slicer value from Year dim table and Division dim table.

Irwan_1-1740699480956.png

Irwan_2-1740699492188.png

 

as the result above, in 2025

Mission Critical has 100 average value in COLA and 2.5 average value in COLB

Operations has 100 average value in COLA and 3 average value in COLB.

 

Hope this will help.

Thank you.

mwJansen - 28Feb25.pbix
Message 4 of 7
306 Views
0
mwjansen
Helper I
Helper I
In response to Irwan

‎03-03-2025 06:39 AM

Thank you @Irwan , everything works as it should now! Much appreciated. 

Message 5 of 7
265 Views
Irwan
Super User
Super User
In response to mwjansen

‎03-03-2025 01:44 PM

Hello @mwjansen 

 

Glad to be a help.

 

Thank you.

Message 6 of 7
250 Views
0
Irwan
Super User
Super User

‎02-26-2025 08:08 PM

hello @mwjansen 

 

i am not sure how the data is off, but i tried your DAX and it looks fine.

Irwan_0-1740629272444.png

Irwan_1-1740629293888.png

Thank you.

Message 2 of 7
342 Views
0

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All