Forum Discussion
How to Count or Average a Measure?
Hi Expert,
I have a follow-up question regarding a previous post. Don't know whether I should follow the same thread or open a new one.
I've asked a question about how to filter and exclude a set of data a couple of weeks back and received a Measure as the link below:
https://community.powerbi.com/t5/Desktop/Filter-and-exclude-the-data/m-p/573548#M270908
The Measure is as below and works fine:
Measure = VAR qulifiedCust = CALCULATETABLE ( VALUES ( Table1[member ID] ), FILTER ( ALL ( Table1 ), 'Table1'[type of stores] = "cosmetics" ) ) RETURN SUMX ( 'Table1', IF ( [member ID] IN qulifiedCust, [invoice amount], 0 ) )
Now I would like to know how to tweak this Measure so I can Count the number of 'invoice amount'? also, Average the 'invoice amount'?
I was trying to do this through Visualization panel then realized you can't do Count or Average to a Measure as the other Columns.
Thanks
Anonymous
To aggregate (SUM,AVERAGE etc) a MEASURE, we typically use an X function (ITERATORS)
Average a MEASURE =
AVERAGEX ( ALLSELECTED ( TableName[ColumnName] ), [Measure] )
OR
Average a MEASURE =
AVERAGEX ( VALUES ( TableName[ColumnName] ), [Measure] )
2 Replies
Anonymous
To aggregate (SUM,AVERAGE etc) a MEASURE, we typically use an X function (ITERATORS)
Average a MEASURE =
AVERAGEX ( ALLSELECTED ( TableName[ColumnName] ), [Measure] )
OR
Average a MEASURE =
AVERAGEX ( VALUES ( TableName[ColumnName] ), [Measure] )Hi Anonymous
You may use COUNTX Funtion as below. If it is not your case, please share more details or the sample data for your scenario so that we could help further on it.
Measure = VAR qulifiedCust = CALCULATETABLE ( VALUES ( Table1[member ID] ), FILTER ( ALL ( Table1 ), 'Table1'[type of stores] = "cosmetics" ) ) RETURN COUNTX ( 'Table1', IF ( [member ID] IN qulifiedCust, [invoice amount], 0 ) )Regards,
Cherie
