Forum Discussion

pawelj795's avatar
pawelj795
Post Prodigy
6 years ago
Solved

Count values based on other column

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.

Need Help
  • MartynRamsden's avatar
    MartynRamsden
    6 years ago

    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.

2 Replies

  • MartynRamsden's avatar
    MartynRamsden
    Solution Sage
    6 years ago

    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's avatar
      MartynRamsden
      Solution Sage
      6 years ago

      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.