Forum Discussion

gkarlo's avatar
gkarlo
Icon for Helper I rankHelper 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 ...
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]
)
govindarajan_d's avatar
govindarajan_d
Icon for Super User rankSuper 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
    Icon for Helper I rankHelper 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 😁

     

    • govindarajan_d's avatar
      govindarajan_d
      Icon for Super User rankSuper User
      2 years ago

      Hi gkarlo,

       

      Did you try using the stacked bar/column chart?

       

       

      • gkarlo's avatar
        gkarlo
        Icon for Helper I rankHelper I
        2 years ago

        Thanks 🙏... btw I have tried to follow that you did with respect on counting the number of projects but regarding the price but I didn't have any success, 

        DATASAMPLE 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.    

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

        PROYECTLIST TABLE

        Project    | Price

        A          | 100€

        A.         |100€

        B          | 50€

        C.         | 50. €

        C.         |  50 €

        D.         |150 €

        D.         | 150€

        E.         |50€

        F         | 50€

        So the result should be:  
        won projects: 100€

        lost projects:   200€

        No realized projects: 150€

         

        For example only I tried to change the given measure in order to sum all prices with respect on this status: 

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

        Instead of COUNTROW, I put SUMX

        and for the table SampleData, I put another table that contains the price (different to the table of projects and status, but those have a linked relationship)

         

        I don't know which is the missing step or in this case there is another way to get the revenue value. Thanks for the given time as well and I hope you may help me 🙏