Average of the Measure

[saud968]

Hi, 

I have a measure under the measure table so there are no columns. KCS = CALCULATE([Colleague Cicks]+ [Colleague Searches]*2 + [Article Attachements]*3). Further, [Colleague Cicks], [Colleague Searches], and [Article Attachements] are all measures as well. 

I need to get an average for the KCS measure. I have achieved an average count for KCS

Avg Count KCS = DIVIDE([KCS],[PDays]) Pdays is also measure 

PDays = SUM(Attendance[Present days])

This is the formula used in excel to achieve the percentile for KCS =IFERROR(ROUND(([@AvgCount]/SUMPRODUCT(AVERAGEIFS([AvgCount],[Product],[@Product],[AvgCount],">0",[AvgCount],"<>")))*100,0),"") 
I need to the same in Power BI, I used the below measures but the output numbers are different then excel. 

KCS Average =

AVERAGEX(

    VALUES('Colleague Events'[CLICK_ID]),  -

    [Colleague Cicks] + [Colleague Searches] * 2 + [Article Attachements] * 3

)

P KCS = CALCULATE(DIVIDE([KCS Average], [Avg Count KCS])*100)

@Ahmedx @Ritaf1983 @Fowmy @PijushRoy @amitchandak 

[amustafa]

First, Calculate the Average of `AvgCount` for Each Product

Average Per Product =
CALCULATE(
AVERAGE([Avg Count KCS]),
FILTER(
'YourTable',
[Avg Count KCS] > 0 && [Product] = EARLIER([Product])
)
)

Then, Calculate the Percentile

Percentile KCS =
IFERROR(
ROUND(
DIVIDE(
[Avg Count KCS],
[Average Per Product]
) * 100,
0
),
BLANK()
)

[saud968]

Thank you for your response Avg Count KCS is a measure so when we use the Average function AVERAGE([Avg Count KCS]) it requires a column...basically all of the above are measures and not column

[Anonymous]

Hi @saud968 ,

Please update the formula of your measures as below and check if they can return the expected result...

KCS Average = 
AVERAGEX(
    ALL('Colleague Events'),
    -[Colleague Cicks] + [Colleague Searches] * 2 + [Article Attachements] * 3
)

P KCS = 
CALCULATE(
    DIVIDE([KCS Average], [Avg Count KCS]) * 100,
    ALL('Colleague Events')
)

If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format, your matrix Fields settings and your expected result with special examples and screenshots based on your shared sample data. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[saud968]

The above did not help I will try to see if I can get the raw data it might difficult but I will try