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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

How can you not sum the averages?

Hello,

 

So the main problem is that I would like to show the average numbers per Week numbers. But I don't know why but it sums up all the averages, and not just average the averages.

My current DAX formula:

 

 

Average = 
AVERAGEX(
	FILTER('Code',Code[Code_name] = "Failure"),
	CALCULATE([Gross Time])
)

 

 

Where Gross Time is another Measure.

image.png

In the picture you can see that calculating the average by ID works, but if you collapse the matrix it shows weird numbers per week number. 
Please help

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@Anonymous do you mean this?

| ID | Sales |
|----|-------|
| A  | 10    |
| A  | 20    |
| B  | 30    |
| B  | 40    |
| B  | 50    |
| B  | 60    |
| C  | 70    |
| D  | 80    |
_customAverage = 
VAR _1 =
    ADDCOLUMNS (
        'Table',
        "average",
            VAR a = 'Table'[ID]
            RETURN
                AVERAGEX ( FILTER ( 'Table', 'Table'[ID] = a ), [_salesSum] )
    )
VAR _2 =
    ADDCOLUMNS (
        _1,
        "rank", RANKX ( FILTER ( _1, [ID] = EARLIER ( [ID] ) ), [_salesSum],, ASC )
    )
VAR _3 =
    ADDCOLUMNS ( _2, "newAvg", IF ( [rank] = 1, [average] ) )
RETURN
    IF (
        HASONEVALUE ( 'Table'[ID] ),
        AVERAGEX ( _1, [average] ),
        AVERAGEX ( _3, [newAvg] )
    )

smpa01_0-1637689360520.png

 

pbix is attached

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

8 REPLIES 8
smpa01
Super User
Super User

@Anonymous do you mean this?

| ID | Sales |
|----|-------|
| A  | 10    |
| A  | 20    |
| B  | 30    |
| B  | 40    |
| B  | 50    |
| B  | 60    |
| C  | 70    |
| D  | 80    |
_customAverage = 
VAR _1 =
    ADDCOLUMNS (
        'Table',
        "average",
            VAR a = 'Table'[ID]
            RETURN
                AVERAGEX ( FILTER ( 'Table', 'Table'[ID] = a ), [_salesSum] )
    )
VAR _2 =
    ADDCOLUMNS (
        _1,
        "rank", RANKX ( FILTER ( _1, [ID] = EARLIER ( [ID] ) ), [_salesSum],, ASC )
    )
VAR _3 =
    ADDCOLUMNS ( _2, "newAvg", IF ( [rank] = 1, [average] ) )
RETURN
    IF (
        HASONEVALUE ( 'Table'[ID] ),
        AVERAGEX ( _1, [average] ),
        AVERAGEX ( _3, [newAvg] )
    )

smpa01_0-1637689360520.png

 

pbix is attached

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@Anonymous  did you have a chance to look into this?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
v-xiaotang
Community Support
Community Support

Hi @Anonymous 

Is the value in your matrix measure or column?

Supposing it's a column, here is an example, 

Measure = AVERAGEX('Table','Table'[Column])

vxiaotang_0-1637660671727.png

 

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

The problem is that it's a measure. With column it would work perfect. But it's a measure.

Hi @Anonymous 

Thanks for your reply and patience, I noticed that I missed your initial description. 

If they are measures, it's also achievable to change the value in Row subtotals.

Besides, I need something like bellow to write specified measure which meets your needs,

(1) some sample data in text, the values of all the fields involved in the measure [Average] & [Gross Time]).

     and the DAX expression of [Gross Time].

(2) or a sample file.

Thanks.

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

v-xiaotang
Community Support
Community Support

Hi @Anonymous 

vxiaotang_0-1636958580220.png

so based on the picture, what's the expected result? Thanks

 

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

It should be

2021.45   2.935

So the Average column should "average the averages" not sum it. 

amitchandak
Super User
Super User

@Anonymous ,

If gross time is Avg, simply do

Average =
CALCULATE([Gross Time], FILTER('Code',Code[Code_name] = "Failure"))

 

or try like


Average =
calculate( AVERAGEX(Values('Code'[Year Week No])
,
[Gross Time]
) ,FILTER('Code',Code[Code_name] = "Failure") )

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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