cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
badger123 Member
Member

Average of measure

Hello, 

 

I have created a measure to calculate weighted average of seasonality. This works and I'm using it in a bar chart. What I am trying to do now is to make all the values positive (which I am able to do using ABS) and then find the average of these values.  

 

Measure = DIVIDE(
    SUMX(
	ADDCOLUMNS(
		SUMMARIZE(
			'Table1',
            'Table1'[key_id],
            'Table1'[region],
            'Table1'[month],
            'Table1'[seasonality_pct],
            "weight",CALCULATE(AVERAGE('Table2'[weight_value]))
		),
		"test", 
        'Table1'[seasonality_pct]*[weight_value]
		
	),
	[test]
), SUMX('Table2','Table2'[weight_value]))

Here is an example of the output I'm getting now in a table visual, with ABS values. What I would like is to get the average of these, so in this case approx. 12.85

 

Capture.PNG

 

Any advice would be great, I cannot figure this out! Thanks in advance

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: Average of measure

You should be able to use the same techniques as here: 
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


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

Proud to be a Datanaut!


badger123 Member
Member

Re: Average of measure

@Greg_Deckler I was being stupid... I've cracked it now! Thanks for your help.

 

Measure4 = 
VAR __table = SUMMARIZE('Table1',[month],"__value",ABS([Measure]))
RETURN
IF(HASONEVALUE('Table1'[month]),ABS([Measure]),SUMX(__table,[__value]))
4 REPLIES 4
Highlighted
Super User
Super User

Re: Average of measure

You should be able to use the same techniques as here: 
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


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

Proud to be a Datanaut!


badger123 Member
Member

Re: Average of measure

Thanks @Greg_Deckler , I followed that logic and made this measure using SUM to test but it doesn't seem to quite work. 

 
Measure2 =
VAR __table = SUMMARIZE('Table1',[key_id],"__value",ABS([Measure]))
RETURN
IF(HASONEVALUE('Table1'[key_id]),ABS([Measure]),SUMX(__table,[__value]))
I think it's because I'm not using the ABS in the right place perhaps? It looks like it's summing up the values (negative and positive) then making the output value positive? 
badger123 Member
Member

Re: Average of measure

@Greg_Deckler I also tried this at the end of my original measure and that produced the same result as Measure2. Do you know where I should put the ABS?

 

Measure3 = CALCULATETABLE(
    VALUES('Table1'),
    'Table1'[key_id],
    ALLSELECTED('Table1')))
badger123 Member
Member

Re: Average of measure

@Greg_Deckler I was being stupid... I've cracked it now! Thanks for your help.

 

Measure4 = 
VAR __table = SUMMARIZE('Table1',[month],"__value",ABS([Measure]))
RETURN
IF(HASONEVALUE('Table1'[month]),ABS([Measure]),SUMX(__table,[__value]))