Weighted Average in Sub Metrics

[mayanknamasys]

Hi everyone,

Metrics in Power BI Service gives us the option to create sub-metrics. I see that there are 4 roll-up options to calculate the option of a Metric from Sub Metric which are Sum, Average, Minimum, Maximum. I want to use a weighted average roll up method because each sub metric will have weights.

Is there any way to achieve this? I have read all the documention but couldn't find anything. 

[mayanknamasys]

Thanks for responding. I understand that calculating the weighted average is possible in DAX but to achieve this, I will have to maintain a separate data source for KPI weights. I was interested to see if it is possible to allocate weights to Sub Metrics itself. 

[Anonymous]

Hi @mayanknamasys ,

Rollup submetrics give you the flexibility to specify how you want your data aggregated. Your data can be aggregated ranging from percent completed, to weighted averages, to overall sums. You can use these rollups to measure progress toward an overall goal.

Refer: Create submetrics that roll up to the metric | Microsoft Learn

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[mayanknamasys]

Thanks again. I have gone through that documentation and I know weighted average is mentioned there. But I don't find any way in the Power BI Service to add a weight to sub metrics. There is only option to add targets and actuals. That's why I am not sure if it is possible.

[Anonymous]

Hi @mayanknamasys ,

You need to create a new measure that calculates the weighted average of the sub-metrics.

Weighted Average =
DIVIDE (
SUMX('Table', 'Table'[Sub-Metric Value] * 'Table'[Weight]), 
SUM('Table'[Weight])
)

For more details, please refer:

Create submetrics that roll up to the metric | Microsoft Learn

Announcing submetric roll ups in Power BI Metrics | Microsoft Power BI Blog | Microsoft Power BI

How to calculate weighted average using DAX in Power BI (datacornering.com)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.