Weighted average using DAX

Question

Weighted average using DAX

Samir abdelmalek 21

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).

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,

Lukas Yu -MSFT 5,826 Reputation points

2020-11-20T09:00:51.19+00:00

Hi,

We've not hear from you back, did you resolve this issue with Alexei's answer?
Samir abdelmalek 21 Reputation points

2020-11-23T09:35:13.823+00:00

Hi,

Not resolved yet. Sorry for the late answer.

2 answers

Your answer

Lukas Yu -MSFT 5,826 Reputation points

2020-11-20T09:00:51.19+00:00

Hi,

We've not hear from you back, did you resolve this issue with Alexei's answer?
Samir abdelmalek 21 Reputation points

2020-11-23T09:35:13.823+00:00

Hi,

Not resolved yet. Sorry for the late answer.

Answer 1

Alexei Stoyanovsky 3,416

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.

Samir abdelmalek 21 Reputation points

2020-11-23T09:28:33.437+00:00

Hi Alexei,

Sorry for the late answer.

As i explained before, the column 'Capacity Plan Measure'[Element Short Desc] contains the name of the KPIs, so the function VALUES will return the list of the KPIs in a single column that's all, so how you can - based on the result of VALUES function - calculate [% d'exploitation] * [Prévisions client en actes] (as they are KPIs in the rows and not columns) ??

Please note that we have a column Value in the fact table "Capacity Values", and this column stocks the value of every KPI from 'Capacity Plan Measure'[Element Short Desc].

So i think, it is necessary the the column Value should be used in the formula of the weighted average.

Waiting for your answer.

Thanks,
Alexei Stoyanovsky 3,416 Reputation points

2020-11-30T09:30:37.087+00:00

average of the measure % d'exploitation

I see now that the use of the term 'measure' didn't let me understand the problem initially. As I see now, the two are not 'measures', but rather values in the 'Element Short Desc' column. For this setup, you'll have to do some preparatory work.
First, you'll need to define the measures that'll be returning the values of these two indicators. This is where the Value column should appear as you correctly noted. It may be tricky to define for the utilization percentage, since it is not aggregatable.
And, you'll have to decide on the list of items that you want to do the averaging over. This is the first parameter of the SUMX function. Note that you'll probably have to create one measure for each list you'll want weighted average over, e.g. one for customers, another for countries, another for products, another by countries*products etc.
Overall, the DIVIDE/SUMX approach holds.

Answer 2

Lukas Yu -MSFT 5,826

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

Share via

Weighted average using DAX

2 answers

Your answer