Count based on multiple criteria

[lea_313]

Hiya

I need to calculate some counts but its proving a bit tricky

Sample data below

Project code ID	Product for	Month	Year	Revenue	Product Header	Stage
1544	Class 1	January	2022	1000	Event	10% booked
1544	Class 1	February	2022	1000	Event	100% delivered
1533	Class 1	January	2022	2500	Face to face	10% booked
1354	Class 5	March	2022	1450	Session	10% booked
1544	Class 1	May	2022	1000	Event	100% delivered
1566	Class 6	December	2022	1500	Session	10% booked
1533	Class 1	February	2022	1500	Face to face	100% delivered
1533	Class 1	April	2022	1000	Face to face	10% booked
1544	Class 1	April	2022	1000	Event	100% delivered

Project code is a code used to group sales together

I need to be able to count, for each unique project code, is it active? and if it is, how many product headers are active by summing the codes?

The break down the steps:

  - For project code 1544, there is 4 lines to consider

  - If any of these lines have a stage of less than 100% delivered, then the project is still active

  - If the project is still active, then the is a count of 1 for the product header 'event'

  - This needs to consider year, but I should be able to add this as a filter to the page/table etc

So the output I want to see from the table above is as follows

Product Header	Year	Count of active projects
Event	2022	1
Face to face	2022	1
Session	2022	1

You'll see there are origninally 4 project codes, but the output only shows 3. This is becasue project code 1566 is fully delivered an no longer active

Thanks

[Anonymous]

@lea_313 , I believe this measure will give you the desired results:

Has Active Project = 
    MAXX(
        'YourTable'
        , VAR vStagePercentPosition = FIND("%", 'YourTable'[Stage], 1, 0) -1
        VAR vStagePercent = VALUE(LEFT('YourTable'[Stage], vStagePercentPosition))
        RETURN
            IF(vStagePercent < 100, "Y", "N")
    )

[lea_313]

I get the following:

Feedback Type:
(Error)

MdxScript(Model) (47, 29) Calculation error in measure [Active Project]: Cannot convert value 'G. 100' of type Text to type Number.

My actual stage is anything that is not G.100% Delivered

Thanks

[Anonymous]

@lea_313 , in the sample data you provided you did not mention that the Stage column can contain values such as "G. 100%". Try this alternative measure:

Has Active Project = 
    MAXX(
        'YourTable'
        , VAR vStagePercentPosition = FIND("100%", 'YourTable'[Stage], 1, 0) 
        RETURN
            IF(vStagePercentPosition = 0, "Y", "N")
    )

[lea_313]

Hiya

Thanks for your help

I've added the measure. I get a Y/N rather than a count of active projects by project code. How do I convert this to a count please?

For example, there are 10 active Coaching, 5 active team in 2022

Thanks

[Anonymous · Accepted solution]

@lea_313 , so you actually do want to count up the number of [Project code ID] that are active? Then I think this measure will work:

Active Project Count = 
    SUMX(
        VALUES('lea_313'[Project code ID])
        ,MAXX('lea_313', IF(FIND("100%", 'lea_313'[Stage], 1, 0) = 0, 1, 0))
    )