Count orders with specific conditions

[gkarlo]

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

[govindarajan_d · Accepted solution]

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]

hi Govindarajan,

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

[govindarajan_d · Accepted solution]

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]

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]

Hi @gkarlo,

Did you try using the stacked bar/column chart?

[gkarlo]

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 🙏

[govindarajan_d]

Hi @gkarlo,

Can you try like this:

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

[gkarlo]

Thanks, I put the code but the measure show only the number of projects but not the sum of prices 😅(as the previous code), 

[govindarajan_d · Accepted solution]

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]
)

[gkarlo]

No worries 😅, thanks too much, it works correctly 🙌

[govindarajan_d]

@gkarlo, Glad it worked!