Forum Discussion

Clement_74's avatar
Clement_74
Frequent Visitor
2 years ago
Solved

fomula countrows filtered by month

Hello,

i am looking for a dax formula in order to measure an OTD (on time delivering) by month.

years are filtered by a segment on all my visualisation and month are managed through a "table date" generate like this:

table = DATATABLE("month", STRING, "Number", INTEGER, {{"janvier", 1}, {"fevrier", 2}, {...}, {"decembre", 12}})

 

so on each visualization i use "month" as "axe" to generate visualizations by month.

 

i am using the formula below:

OTD = 100 - (DIVIDE(CALCULATE(COUNTROWS(DDB), DDB[Delay]>0), COUNTROWS(DDB))*100)

howerver the formula above seems don't works correctly. Manual checks revealed issues.

it seems that i need to set a filter by month inside the formula... how can i set this?

best regards

Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Clement_74 ,

     

    Try to modify your formula like below:

    OTD =
100
    - (
        DIVIDE (
            CALCULATE (
                COUNTROWS ( DDB ),
                DDB[Delay] > 0,
                FILTER ( ALL ( 'table' ), 'table'[month] = VALUES ( 'table'[month] ) )
            ),
            CALCULATE (
                COUNTROWS ( DDB ),
                FILTER ( ALL ( 'table' ), 'table'[month] = VALUES ( 'table'[month] ) )
            )
        ) * 100
    )

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Clement_74 ,

     

    Try to modify your formula like below:

    OTD =
100
    - (
        DIVIDE (
            CALCULATE (
                COUNTROWS ( DDB ),
                DDB[Delay] > 0,
                FILTER ( ALL ( 'table' ), 'table'[month] = VALUES ( 'table'[month] ) )
            ),
            CALCULATE (
                COUNTROWS ( DDB ),
                FILTER ( ALL ( 'table' ), 'table'[month] = VALUES ( 'table'[month] ) )
            )
        ) * 100
    )

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.