calculate weighted index

[flunte70]

I am trying to make a Measure where I want to calculate weighted index by the following formula:

Weighted CAP index = [CAP Index]*[Actuals]/"the Total of [Actuals]"

but I don't know how to refer to "the Total of [Actuals]"?

If you don't know what I mean I will make a screendump...

[AndrejZitnay]

Hello @flunte70

I would try

Weighted CAP index = SUMX(Sheet1,Sheett1[CAP index}*Sheet1[Actuals])/SUM(Sheet1[Actuals])

This is how weighting average works for me.

Imagine that your data are on Sheet1.

I am assuming that CAP index is percantage and you have to do weighing by Actuals.

Kind regards.

Andrej

[MFelix]

Hi @flunte70,

No sure if this is what you need but believe that you need to have a formula like this for the total of actuals:

Total actuals = CALCULATE ([Actuals], Table[Date] <= MAX (Table[Date])

But witouth any further insights not sure how this relates to your data.

Regards,

MFelix

[flunte70]

Hi MFelix,

I get the error "A Function ´MAX` has been used in a True/False expression..." so I don't know what I do wrong?

Sum Actual Max = CALCULATE([Actuals]; Data[Service Component] <= Max (Data[service Component]))

I have also tried to use:

Sum Actuals = CALCULATE([Actuals];ALL(Data[SC specifikation])) but that only gives me the same values as [Actuals]...

Below I have made a screendump of my report (drill down) and my tables

[MFelix]

Change your measure to:

Sum Actual Max =
VAR MAX_Component =
    MAX ( Data[service Component] )
RETURN
    CALCULATE ( [Actuals]; Data[Service Component] <= MAX_Component )

Should do the trick.

Regards,

MFelix

[flunte70]

MFelix - thanks for the suggestion - unfortunately it is not solving the issue as the amount is still the same and not the total...

[MFelix]

Can you say what numbers you want to divide?

[flunte70]

Off course my friend 🙂

I want to end up with a measure based on the following formula:

"KAP index" x "Actuals" : "Total Actuals"

So I thought that I should first make a measure that gives the "Total Actuals" in every shown row (in above screendump it is the Total amount in the 3rd column = 114.793.576).

When I have this measure, I want to calculate the Weighted capacity by the formula: "KAP index" x "Actuals" : "Total Actuals"

(In above screendump the first line should calculate 87 * 327.760 : 114.793.576).

[MFelix]

Hi @flunte70,

The problem is the way you are calculating your formula, giving it a max it will no sum all the values of the actuals you need to redo the formula like this:

Height =
DIVIDE (
    [KAP Index] * [Actuals],
    CALCULATE ( [Actuals], ALL ( Table[Nummer] ) )
)

I'm assuming that Actuals and KAP index are measures.

Regards,

MFelix

[flunte70]

Thank you very much for your time...

Unfortunately it still doesn't work; is it because both "Nummer" and "SC specifikation" is not measures and that we need to include the "SC specifikation" column?

By the way; I got an error with you formel an corrected ( Table[Nummer] ) to ( Data[Nummer] ) as "Data" is the name of the table.

[MFelix]

Hi @flunte70,

didn't know the name of your table that's why i put Table 😄

Place also in the ALL statement the SC specifikation.

regards,

MFelix

[flunte70]

YES - thanks a lot, MFelix!; that soved the issue with calculating each row; so now I need to summarize all these values in the Total of this column in stead of using the same formula on the Total line...

[MFelix · Accepted solution]

Hi @flunte70,

Create a new measure and use it on your column:

Height Total =
IF (
    HASONEVALUE ( Data[Nummer] ),
    [Height],
    SUMX ( ALL ( Data[Nummer], Data[SC specifikation] ), [Height] )
)

Regards,

MFelix

[flunte70]

**bleep**, I forgot to test it when I use filter 😞

The measure:

TEST Vægtet KAP = IF ( HASONEVALUE ( Data[Nummer] ); DIVIDE ([KAP Index] * [Actuals]; CALCULATE ( [Actuals]; ALL ( Data[Nummer]; Data[SC specifikation] ))); SUMX ( ALL ( Data[Nummer]; Data[SC specifikation] );DIVIDE ([KAP Index] * [Actuals]; CALCULATE ( [Actuals]; ALL ( Data[Nummer]; Data[SC specifikation] ))) ) )

works when I look at all the records:

but not if I apply a filter:

The filter is based on a small table where I categorize "Nummer":

MFelix - if you are not already tired of me - I hope that this is the last question regarding this issue 🙂

[MFelix]

Hi @flunte70,

Try to make two formulas one for Heigth and another for total Height that uses the previous one. If try to make it a full dax measure the context will have to be changed.

Regards,

Mfelix

[flunte70]

Hi MFelix,

I have the flw:

?Height = CALCULATE ( [Actuals]; ALL ( Data[Nummer]; Data[SC specifikation] ) )

?Vægtet KAP OLD = DIVIDE ([KAP Index] * [Actuals]; CALCULATE ( [Actuals]; ALL ( Data[Nummer]; Data[SC specifikation] )))

?Vægtet KAP = IF ( HASONEVALUE ( Data[Nummer] ); [?Vægtet KAP OLD]; SUMX ( ALL ( Data[Nummer]; Data[SC specifikation] ); [?Vægtet KAP OLD] ) )

The ? before the name is to keap them in the top of my measures.

"?Height" is returning the Total for all rows which is ok when my "Kategori" filter is off.

I think that the problem with my filter is that it is in a different table as I thought I could enter it in the ALL formula.

"?Vægtet KAP OLD" is returning the weighted CAPS in all the rows but the total is wrong

"?Vægtet KAP" is returning the weighted CAPS in all the rows and the total is right - but not when I filter on "Kategori"?

[flunte70]

Thanks a lot MFelix - you have saved my ass 🙂

Merry Christmas & a Happy New Year to you and your family!