Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Any advice would be great, I cannot figure this out! Thanks in advance
Solved! Go to Solution.
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
@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]))
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
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]))
@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]))
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |