Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
JustNewbie
Helper I
Helper I

Dax measure for Grand total

JustNewbie_0-1688459311703.png

Hi, I have 2 connected tables, how do I use Dax measures to perform the calculations. I intend to calculate the avg days of each activities and total up  & give me final numbers. The DAX need to calculate average of each activities and finally total up all activities. I need to measure 4 activities took up how many days to complete.

1 ACCEPTED SOLUTION
AlanFredes
Resolver IV
Resolver IV

Hi,

 

You will need to use AVERAGEX to get the average of each column across their respective tables and then sum up each result.
It would look something like this:


Sum Avg Activities =
VAR Avg_Days_To_assigne = AVERAGEX('Table 1',SUM('Tabl 1'[Days to assigne])
VAR Avg_Days_to_pack = AVERAGEX('Table 1',SUM('Tabl 1'[Days to pack])
VAR Avg_Days_To_ship = AVERAGEX('Table 2',SUM('Tabl 2'[Days to ship])
VAR Avg_Days_To_receive = AVERAGEX('Table 2',SUM('Tabl 2'[Days to receive])
RETURN
Avg_Days_To_assigne + Avg_Days_to_pack + Avg_Days_To_ship + Avg_Days_To_receive

 

Did I answer your question? Mark my post as a solution!

View solution in original post

4 REPLIES 4
AlanFredes
Resolver IV
Resolver IV

Hi,

 

I would need some more information to know exactly what issue you are facing. Example data would be prefered.

Note that the AVERAGEX formula I provided will run through each row in the table if your Item column is a subset of these (a categoria) displayed just in a Matrix or Table then you need to change the formula like shown bellow:
VAR Avg_Days_To_assigne = AVERAGEX(VALUES('Table X'[Item Column]),CALCULATE(SUM('Tabl 1'[Days to assigned]))

Thanks, i got your idea. I have solved the issue. 

AlanFredes
Resolver IV
Resolver IV

Hi,

 

You will need to use AVERAGEX to get the average of each column across their respective tables and then sum up each result.
It would look something like this:


Sum Avg Activities =
VAR Avg_Days_To_assigne = AVERAGEX('Table 1',SUM('Tabl 1'[Days to assigne])
VAR Avg_Days_to_pack = AVERAGEX('Table 1',SUM('Tabl 1'[Days to pack])
VAR Avg_Days_To_ship = AVERAGEX('Table 2',SUM('Tabl 2'[Days to ship])
VAR Avg_Days_To_receive = AVERAGEX('Table 2',SUM('Tabl 2'[Days to receive])
RETURN
Avg_Days_To_assigne + Avg_Days_to_pack + Avg_Days_To_ship + Avg_Days_To_receive

 

Did I answer your question? Mark my post as a solution!

Thanks for your reply.... I tried to applied your solution to my real dataset. But i realised it gives me wrong results.. Not sure what went wrong? For example, If I total up all the average activities, it should give me 18 days.

JustNewbie_1-1688546336710.png

 



JustNewbie_0-1688545573310.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.