Weighted average using DAX

Samir abdelmalek 21 Reputation points
2020-11-17T13:33:21.9+00:00

Hello,

I prepared a tabular cube in SSAS,

I have a dimension table "Capacity Plan Measure" which has the column "Element Short Desc" that contains a list of indicators, other dimensions and a fact table "Capacity Values" that contains a column "Value" (it's the value of each indicator of the "Element Short Desc" column).

40452-capacity-plan-measure-dimension.png

40396-value.png

I want to create the weighted average of the measure % d'exploitation by the measure Prévisions client en actes

WA = sum(% d'exploitation * Prévisions client en actes) / sum(Prévisions client en actes)

i know the formula but i don't know how to create it using DAX.

Do you have any suggestions ? If yes, please write to me

Thanks,

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,344 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Alexei Stoyanovsky 3,416 Reputation points
    2020-11-18T07:26:45.82+00:00
    DIVIDE (
        SUMX (
            VALUES ( 'Capacity Plan Measure'[Element Short Desc] ),
            [% d'exploitation] * [Prévisions client en actes]
        ),
        [Prévisions client en actes]
    )
    

    That's assuming the [Prévisions client en actes] is additive, which it looks like. If it's not, it'll have to be wrapped in the same SUMX in the denominator, too.

    1 person found this answer helpful.

  2. Lukas Yu -MSFT 5,826 Reputation points
    2020-11-27T09:12:25.947+00:00

    Hi,
    Check this approach see if it fit for your need. Weighted average using DAX

    Or this tutorial give the rate accordingly: Weighted Average Rate using DAX

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.