Forum Discussion

flunte70's avatar
flunte70
Icon for Helper I rankHelper I
8 years ago
Solved

calculate weighted index

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 re...
Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    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's avatar
flunte70
Icon for Helper I rankHelper I
8 years ago

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's avatar
MFelix
Icon for Super User rankSuper User
8 years ago

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's avatar
    flunte70
    Icon for Helper I rankHelper I
    8 years ago

    Thanks a lot MFelix - you have saved my ass :-)

     

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

  • flunte70's avatar
    flunte70
    Icon for Helper I rankHelper I
    8 years ago

    **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's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago

    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's avatar
    flunte70
    Icon for Helper I rankHelper I
    8 years ago

    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"?