CODEWITHSUNDEEP
exceldaxpowerpivot
Christian Marin
Christian Marin

Reputation: 39

Summarizing values for a calculated column

Good morning all! Got a head-scratcher this morning, thanks for taking a look.

It's a real pickle but here goes:

Table A has a field by which we assign a numeric value corresponding to a progress state per line, as follows:

0 = Not Started
99 = In Progress
100 = Complete Each line also has an ID, resulting in something like this:

enter image description here

The problem to overcome is:

We need another field to summarize the overall progress by ID, but the permutations of complete criteria are unusual. If all parts are 0, then 0. If one part is not 0 and the other part is 0, then 99. If both parts are not 0 and not 100, then 99. If both parts are 100, then 100.

The last part of this is the the result MUST be in a column, with the end result being:

enter image description here

I've tried various tricks that have worked for similar things in the past, to no avail this morning. I think the thing thats getting me is returning the same value on two lines per ID. Any and all help is very much appreciated, as always thanks to the community for taking a look.

Upvotes: 1

Views: 89

Answers (1)

sergiom
sergiom

Reputation: 4877

So the rule is: if all parts are 100 then 100, if all parts are 0 then 0, otherwise 99.

If this is correct, then counting the different statuses per ID could do the job

Overall = 
VAR T0 =
    CALCULATE( COUNTROWS( A ), ALLEXCEPT( A, A[ID] ), A[Progress] = "0" )
VAR T99 =
    CALCULATE( COUNTROWS( A ), ALLEXCEPT( A, A[ID] ), A[Progress] = "99" )
VAR T100 =
    CALCULATE( COUNTROWS( A ), ALLEXCEPT( A, A[ID] ), A[Progress] = "100" )
VAR Result =
    IF(
        T0 > 0
            && T99 = 0
            && T100 = 0,
        "0",
        IF( T100 > 0 && T0 = 0 && T99 = 0, "100", "99" )
    )
RETURN
    Result

Upvotes: 1

Related Questions