Is there any option to restrict the tool like not to perform the weighted average?

[Vaishnavi_M]

Hi ,

The bullet graph works with the weighted average as below say Prj1 :16.67 and Prj2 : 52.50 if we take simple average it shows 37.14  but we just want the simple average of these two says (16.67+52.50)/2 = 34.585.

 The scenario is explained with the exact simple example .Kindly help us.

In this scenario we need average value @ Project level is 39.165.

But we are getting 38.57.

If anyone have any solution please let us know to solve the issue.

Thanks in Advance.

[tringuyenminh92]

Hi @Vaishnavi_M,

Please try this approach:

• Using Calculated Column to compute Average @ Project lvl : total value / number of sprint name in that project
• Choose Formatting with 2 decimal places

Average @ Project lvl = CALCULATE( SUM(Data[Value])/DISTINCTCOUNT(Data[Sprint Name]),filter(all(data) ,Data[Project Name]=EARLIER(Data[Project Name]) ) )

• Using Calculated Column to compute Average @ overall Project:
• Choose Formatting with 3 decimal places

Average @ overall Project = DIVIDE( SUMX(VALUES(Data[Average @ Project lvl]),Data[Average @ Project lvl]),DISTINCTCOUNT(Data[Project Name]))

(With average overrall you could put it into table or let it separate in Card as first picture)

If this works for you please accept it as solution and also like to give KUDOS.

[Vaishnavi_M]

Hi ,

Your Solution helped to solve little bit .

Let me explain you the full scenario in detail.

Img 1Img 2Img 3Img 4

In Img 1:

1. U could see 4 cards displayed  and a table. 

2. 1st card is the weighted average value  that the power BI is throwing, 2nd card i tried with your DAX expressions , 3rd and 4rd card are just the distint count of the Projects and the Sprints selected.

3.  The 2nd card (card project lvl) shows the average of the the critical and the high defect  column in the table .

In Img2 :

1. The 1st card (Average of project lvl) shows the weighted  average of the the critical and the high defect  column in the table  when two projects are selected. At  this point i want the tool to perform the simple average just the (16.67+52.50)/2= 34.585

2. Since the above step is not achivable , i slightly modified your DAX expressions then in the 2nd Card(card project lvl) i could get the sum of the projects or the sprints dynamically.

3. Now im unable to divide this 2nd Card(card project lvl) by the count of Sprint /Projects.

Img 3:

1.If i select single project that has 4 sprints, but let us assume the user i selcting 2 sprints under a single project , i should get only the average of those two sprints say (60+100)/2 =80.

Img 4:

1. Since you could see the Img 4 where we have 4 levels like portfolio, program ,project and Sprints . 

Collection of Sprint -> Projects 

Collection of Projects -> Programs

Collection of Programs-> Portfolio

2. The user may select at any level in the right pane(i.e) Page level filters.

3. We want the simple average at that level.

In General,

At the moment user tries to select any porfolio, program,project and sprint , it should accordingly give me simple averaege of the Critical and high defect column in the table.

Since Im new to DAX , Im struggling to get the things done when selected dynamic.

Thanks in Advance.

[tringuyenminh92]

Hi @Vaishnavi_M,

Could you masking some sensitive data and then share me your transaction data? so i could quickly do some guessing to figure out solution for your case.

[Vaishnavi_M]

Hi ,

Can you share us your microsoft id in the private message. So that we can send the workspace, where you could get clear picture on the issue .

Thanks In Advance.

[BhaveshPatel]

You should explicitly create the measures of average values for Project 1 & 2 using  CALCULATE FUNCTION. 

Avgof Proj1:=CALCULATE(AVERAGE(Project[Value]),Project[ProjectName]="P1")

Similarly create the one for project 2.

AvgTotal:=[Avg of Proj1]+[Avg of Proj 2]

Sum up both the measures and use DIVIDE & DISTINCTCOUNT as below

Final Average:=DIVIDE([AvgTotal],DISTINCTCOUNT(Project[ProjectName]),2)