Forum Discussion

gkarlo's avatar
gkarlo
Helper I
2 years ago
Solved

Count orders with specific conditions

I hope someone may assist me with this...

Currently I have a table of projects that can be Won, Lost or No Realized, the column of this os really exisiting, the problem that I have is that the same project can have No Realized and Won Status in different dates, or Not Realized and Lost Status. However, I would like to know how many orders I have with No Realized Status if the project doesn't have status of Won or Lost previously

example of the table

-----------------------------

Project | Status

A          | No Realized

A.         | Won

B          | No Realized

C.         | No Realized

C.         | Lost

D.         | No Realized

D.         | Lost

E.         | No Realized

F         | No Realized

---------------------------

So the result should be:  
won projects: 1

lost projects:   2

No realized projects: 3

 

Help using powerbi
Need Urgent Help
powerbi
  • govindarajan_d's avatar
    govindarajan_d
    2 years ago

    Hi gkarlo,

     

    Create a calculated column like this:

     

    StatusValue =
IF (
    SampleData[Status] = "No Realized",
    1,
    IF ( SampleData[Status] = "Lost", 2, 3 )
)

     

     

    And then create 3 measures like this:

     

    No of Won =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            SampleData,
            SampleData[Project],
            "MaxStatus", MAX ( SampleData[StatusValue] )
        ),
        [MaxStatus] = 3
    )
)
    No of Lost =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            SampleData,
            SampleData[Project],
            "MaxStatus", MAX ( SampleData[StatusValue] )
        ),
        [MaxStatus] = 2
    )
)
    No of No Realized =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            SampleData,
            SampleData[Project],
            "MaxStatus", MAX ( SampleData[StatusValue] )
        ),
        [MaxStatus] = 1
    )
)

     

     

    The same can be achieved using RANK formula also. 

     

    Tested:

     

    Upvote and accept as a solution if it helped!

     

  • govindarajan_d's avatar
    govindarajan_d
    2 years ago

    Hi gkarlo,

     

    Sorry about that. I missed to replace COUNTROWS.

     

    No of No Realized =
SUMX (
    FILTER (
        ADDCOLUMNS(
        SUMMARIZE (
            SampleData,
            SampleData[Project],
            "MaxStatus", MAX ( SampleData[StatusValue] )
        ),
        "SumProject",SUMX(RELATEDTABLE(ProjectList),ProjectList[Price])
        ),
        [MaxStatus] = 1
    ),
[SumProject]
)

10 Replies

  • govindarajan_d's avatar
    govindarajan_d
    Super User
    2 years ago

    Hi gkarlo,

     

    Create a calculated column like this:

     

    StatusValue =
IF (
    SampleData[Status] = "No Realized",
    1,
    IF ( SampleData[Status] = "Lost", 2, 3 )
)

     

     

    And then create 3 measures like this:

     

    No of Won =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            SampleData,
            SampleData[Project],
            "MaxStatus", MAX ( SampleData[StatusValue] )
        ),
        [MaxStatus] = 3
    )
)
    No of Lost =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            SampleData,
            SampleData[Project],
            "MaxStatus", MAX ( SampleData[StatusValue] )
        ),
        [MaxStatus] = 2
    )
)
    No of No Realized =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            SampleData,
            SampleData[Project],
            "MaxStatus", MAX ( SampleData[StatusValue] )
        ),
        [MaxStatus] = 1
    )
)

     

     

    The same can be achieved using RANK formula also. 

     

    Tested:

     

    Upvote and accept as a solution if it helped!

     

    • gkarlo's avatar
      gkarlo
      Helper I
      2 years ago

      Hi again 👋👋, 

      if I would like to have the three measures in one column chart, how could I do it? cause in x-axes is not possible on power BI...  something like this, I would really apreciate your support 😁

       

  • gkarlo's avatar
    gkarlo
    Helper I
    2 years ago

    hi Govindarajan,

    I tested it, so far it worked very nice... thanks for your support 👍