Forum Discussion
Measure: return the max average(by months)
Hi, I've started with Power BI a week ago. I found that was the perfect tool for what i was asked for. I've been chosen to get the time measures of the processes of my company. The metric chosen ha...
Anonymous can you try the above?
avgByProdMaxYear = CALCULATE ( AVERAGE ( 'Sample'[LT] ), VAR _base = GROUPBY ( 'Sample', 'Sample'[Product], 'Sample'[Year], "avg", AVERAGEX ( CURRENTGROUP (), 'Sample'[LT] ) ) VAR _rank = SUMMARIZE ( FILTER ( ADDCOLUMNS ( _base, "rank", RANKX ( FILTER ( _base, [Product] = EARLIER ( [Product] ) ), [avg],, DESC ) ), [rank] = 1 ), 'Sample'[Product], 'Sample'[Year] ) RETURN _rank )Anonymous please refer to the attached pbix
smpa01
Community Champion
Community ChampionAnonymous can you try the above?
avgByProdMaxYear =
CALCULATE (
AVERAGE ( 'Sample'[LT] ),
VAR _base =
GROUPBY (
'Sample',
'Sample'[Product],
'Sample'[Year],
"avg", AVERAGEX ( CURRENTGROUP (), 'Sample'[LT] )
)
VAR _rank =
SUMMARIZE (
FILTER (
ADDCOLUMNS (
_base,
"rank", RANKX ( FILTER ( _base, [Product] = EARLIER ( [Product] ) ), [avg],, DESC )
),
[rank] = 1
),
'Sample'[Product],
'Sample'[Year]
)
RETURN
_rank
)
Looks like it works. Thank you very much.
- smpa01
Anonymous while this works, I want to leave you with a more elegant 1 line measure. Pbix is attached.
_top1Average = CALCULATE ( [average], KEEPFILTERS ( TOPN ( 1, ALLSELECTED ( Sample_data[Year] ), [average], DESC ) ) )Thank you but this second solution does not work for me. I will try it later when I'll get a bertter understanding of DAX and M.
The error that I get is that there is no column [average].
- smpa01
Anonymous please refer to the attached pbix