Count values based on other column

[pawelj795]

Hi,
I have matrix like below with status of my sales orders.


Matrix shows list of my orders with specified sales lines.
I find order as "delay" if any line of particular order are "delay".

Now, I want to count and show in pie chart how many orders are delay and how many are on time.
The main issue is, that I don't want to count every line, only whole orders.

[MartynRamsden]

Hi @pawelj795 

A simple DISTINCTCOUNT measure should do the trick:

No Of Orders = DISTINCTCOUNT ( Table1[TRANSREFID] )

Add that to your pie chart, along with the Table1[Order status] column and you should get the result your looking for.

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.

[MartynRamsden · Accepted solution]

Sorry @pawelj795 

Please ignore my previous post - I misread your question.

Try these measures instead:

On Time Orders =
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            VALUES( Table1[TRANSREFID] ),
            "@Status", CALCULATE( MIN( Table1[Order status] ) )
        ),
        [@Status] = "on time"
    )
)

Delayed Orders =
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            VALUES( Table1[TRANSREFID] ),
            "@Status", CALCULATE( MIN( Table1[Order status] ) )
        ),
        [@Status] = "delay"
    )
)

Simply add both to your pie chart under the 'Values' field well.

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.