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_HRS])+sum(V_COMB_PROD_CRW1[TOTAL_DOWNTIME_HRS]))-sum(V_COMB_PROD_CRW1[PLANNED_DOWN_TIME])))*100
Problem is:  i need to calculate those results, but only one time for each dayprod_ID. I've tried SUMX with distinct formulas, but i can't get the right way to do it.
 
Is there a more advanced user that can give me a hint??
 
Thanks,
Dany
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] )
)

     

2 Replies

  • 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] )
)