Forum Discussion

DGodQc's avatar
DGodQc
Frequent Visitor
6 years ago
Solved

Calculate sum with a distinct filter

Hello community,   I have problems calculating an availability % for my production. Here is my formula at the moment: Availability = (sum(V_COMB_PROD_CRW1[PROD_HRS])/((sum(V_COMB_PROD_CRW1[PROD_H...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi DGodQc ,

     

    You could add an index column with query editor. Then use RANKX() function to create a rank column.

    rank =
RANKX (
    FILTER ( testTable, testTable[ID] = EARLIER ( testTable[ID] ) ),
    testTable[Index],
    ,
    ASC,
    DENSE
)

    Then sum the value where [rank]=1.

    Measure =
CALCULATE (
    SUM ( testTable[value] ),
    testTable[rank] = 1,
    ALLEXCEPT ( testTable, testTable[ID] )
)

     

v-eachen-msft's avatar
v-eachen-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi DGodQc ,

 

You could add an index column with query editor. Then use RANKX() function to create a rank column.

rank =
RANKX (
    FILTER ( testTable, testTable[ID] = EARLIER ( testTable[ID] ) ),
    testTable[Index],
    ,
    ASC,
    DENSE
)

Then sum the value where [rank]=1.

Measure =
CALCULATE (
    SUM ( testTable[value] ),
    testTable[rank] = 1,
    ALLEXCEPT ( testTable, testTable[ID] )
)