Calculating a mean/average based on a measure

[Anonymous]

Hello guys,

I have the following measure which counts the number of occurrences of product IDs within a column.

Occurrence = COUNT('Table'[Product ID])

Since I would like to find out how often product IDs recur on average within the column "Product ID", is there a way to calculate a mean/average based on the above measure?

Hope you can help 🙂

[Anonymous]

Hi @Anonymous ,

Did I answer your question? Please mark my reply as solution, thank you~

Best Regards,

Eyelyn Qin

[Anonymous]

Hi @Anonymous ,

According to my understanding, you want to calculate frequency of each ProductID based on a measure—“Occurrence”, right?

You could use the following formula:

Occurrence =
CALCULATE (
    COUNT ( 'Table'[ProductID] ),
    ALLEXCEPT ( 'Table', 'Table'[ProductID] )
)

AVG =
[Occurrence] / CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table' ) )

My visualization looks like this:

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[amitchandak]

@Anonymous , Try a new measure like

Avg Occurence = averagex(summarize('Table'[Product ID],"_1", COUNT('Table'[Product ID])),[_1])

[Anonymous]

Hi @Anonymous 

I believe what you are looking for is if 

Product ID

A

B

A

Then you need the output as 

product ID	Occurrence
A	.66    or 66%
B	.33    or 33%

Occurrence = count('Table'[Product ID])/CALCULATE(COUNTROWS('Table'),ALLSELECTED('Table'))

 IF you need it in percentage convert the measure to %

[Greg_Deckler]

@Anonymous - This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

[mahoneypat]

Please try this expression in a measure

Avg Occurence = AVERAGEX(VALUES('Table'[Product ID]), [Occurence])

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat