Total Sumx of Tickets State with filtered when they completed

[BileMG]

Hi,

i am struggeling since hours with one Problem and wrong total by using Sumx.

I have a Dateset that contains for each Task each State and when the State started and end.

I want to make a kind of Process Analyisis so i wan to calculate the Average of States that all the Task has, but only when the Task is been completed.

My Dataset ist row Based, so that i have for each Task and State an own row.

I tried the following measueres.

To get the amount of Tasks per Ticket

_States_per_Ticket (completed) = SUMX('Tabelle1 (2)';CALCULATE(COUNT('Tabelle1 (2)'[State]);FILTER('Tabelle1 (2)';Max('Tabelle1 (2)'[Start Date State])&&'Tabelle1 (2)'[State]="completed")))

to get the amount of all Tickets that are completed 

_Anz Tickets (completed) = CALCULATE(DISTINCTCOUNT('Tabelle1 (2)'[Task]);'Tabelle1 (2)'[State]="completed")

In an table view the calculation are right, for task 815 there are 4 States and for 819 there are 7. 850 will not be counted because it is not completed. But in Sum PBI show 26, instead of 11. So that the average Divide(26,2) will show 13 instead of 5,5 (11/2)

What could be the Problem ?

thx for help

[v-yuta-msft · Accepted solution]

@BileMG ,

You may modify the first measure using dax below:

_States_per_Ticket (completed) =
SUMX (
    'Tabelle1 (2)';
    CALCULATE (
        COUNT ( 'Tabelle1 (2)'[State] );
        FILTER (
            ALLEXCEPT ( 'Tabelle1 (2)'; 'Tabelle1 (2)'[Task] );
            MAX ( 'Tabelle1 (2)'[Start Date State] )
                && 'Tabelle1 (2)'[State] = "completed"
        )
    )
)

Community Support Team _ Jimmy Tao

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