CALCULATE INDEX SCORE

[Anonymous]

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.

[v-easonf-msft]

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

[tamerj1 · Accepted solution]

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