need help with formula with condition in dividend

[reinaerdt]

I have a data set with items to be produced and per item the number of days the production is delayed.

This dataset can be limited with a slicer. I need a line-chart with on the x-axis the 'number of days delayed' and value must be (sum of items delayed per day) / (total sum of items)

item    days

1234    1

1235    2

1236    2

1237    3

1238    3

1239    3

1240    3

values will be:

for 1 day overdue: (sum items which are 1 day overdue) / total items = 1 / 7 = 0.14

for 2 days overdue: (sum items which are 2 days overdue) / total items = 2 / 7 = 0.28

for 3 days overdue: (sum items which are 3 days overdue) / total items = 4 / 7 = 0.56

 

how can I achieve this?

[reinaerdt · Accepted solution]

found it.

the formula isn't very compact, but it works:

doorloop =
DIVIDE (
    CALCULATE (
        COUNTA ( bideuren[item] ),
        FILTER (
            ALL ( biDeuren[days] ),
            biDeuren[days] <= MAX ( biDeuren[days] )
        )
    ),
    CALCULATE (
        CALCULATE (
            DISTINCTCOUNT ( biDeuren[item] ),
            ALLSELECTED ( biDeuren[item] )
        ),
        ALLSELECTED ( bideuren )
    )
)

[reinaerdt]

I almost have the solution.

To explore the DAX functions and see the outcome, I made some 'Measures', and put them in a table visual. See right visual.

There is a column MeasureCountRows. The value is 9692, the total number of records in my database.

I dont want to use the grandtotal, but the total within the selection, in this case 893. So al the rows must contain 893.

[reinaerdt]

I made a mistake, the formula should be:

values will be:

for 1 day overdue: (sum items which are 1 day overdue) / total items = 1 / 7 = 0.14

for 2 days overdue: (sum items which are between 1 and 2 days overdue) / total items = 3 / 7 = 0.42

for 3 days overdue: (sum items which are between 1 and 3 days overdue) / total items = 7 / 7 = 1.00

[SivaMani]

@reinaerdt,

Try this,

No of days delayed =
DIVIDE(COUNT(Table1[Items]),CALCULATE(COUNTROWS(Table1),ALL(Table1)))

Make sure that days field in Axis.