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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
badger123
Resolver I
Resolver I

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
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

@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]))

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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? 

@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')))

@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]))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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