Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

CALCULATE INDEX SCORE

Hello, I am new to PowerBI (working less than a year) so I'm still learning complicated DAX operators and functions. I'm working to build a new measure which is an index score to normalized the numbers across population. I tried it first on Microsoft Excel for illustration purpose.

 

The Fig 1. is the normal sample dataset both in tabular and raw format. Fig 2. is the index score I am testing . Can someone please help me build a DAX formula that will return the values on Fig. 2? The formula is simple (refer to Fig. 1.1), it's just (C2/C$18)/($N2/$N$18)*100 . The additional requirement though is I need to create the Index Score measure for Type 1 then Type 2 so I think there will be a FILTER formula somewhere. I will really appreciate your input in here. Thank you.

 

Fig 1.1 Tabular

Fig. 1.2. Raw

 

 

Fig. 2.

 

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    I'm not 100% sure about what exactly is required but I've created a couple of measures for you. Please let me know if you need any changes. Here is a sample file https://www.dropbox.com/t/m1kXUWePL8cyrvt9

     

    Index = 
VAR Numbers =
    SUM ( Data[Numbers] )
VAR Class2Total =
    CALCULATE ( 
        SUM ( Data[Numbers] ),
        ALLEXCEPT ( Data, Data[Classification 2] )
    )
VAR Class1TypeTotal =
    CALCULATE ( 
        SUM ( Data[Numbers] ),
        ALLEXCEPT ( Data, Data[Type], Data[Classification 1] )
    )
VAR GrandTotal =
    CALCULATE ( 
        SUM ( Data[Numbers] ),
        REMOVEFILTERS ( )
    )
VAR Index =
    DIVIDE ( 100 * Numbers * GrandTotal, Class1TypeTotal * Class2Total )
RETURN
    Index

     

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 
    I'm not 100% sure about what exactly is required but I've created a couple of measures for you. Please let me know if you need any changes. Here is a sample file https://www.dropbox.com/t/m1kXUWePL8cyrvt9

     

    Index = 
VAR Numbers =
    SUM ( Data[Numbers] )
VAR Class2Total =
    CALCULATE ( 
        SUM ( Data[Numbers] ),
        ALLEXCEPT ( Data, Data[Classification 2] )
    )
VAR Class1TypeTotal =
    CALCULATE ( 
        SUM ( Data[Numbers] ),
        ALLEXCEPT ( Data, Data[Type], Data[Classification 1] )
    )
VAR GrandTotal =
    CALCULATE ( 
        SUM ( Data[Numbers] ),
        REMOVEFILTERS ( )
    )
VAR Index =
    DIVIDE ( 100 * Numbers * GrandTotal, Class1TypeTotal * Class2Total )
RETURN
    Index

     

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    4 years ago

    Hi, Anonymous 

    Could you please tell me whether your problem has been solved?

    If yes, you could accept the helpful answer as solution to colse this thread.

    If you still need help, please provide sanitized sample data and expected result that fully cover your issue.

     

    Best Regards,
    Community Support Team _ Eason