Forum Discussion
Count based on multiple criteria
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
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)) )
5 Replies
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") )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
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") )
