calculate number of projects by area using a group by

[knowledgesearch]

I have the following table and I want to calculate how many tasks based on project phase over plan, initiate and go live. These are 3 seperate measures which will calculate count, the number of tasks by market (UK, DE, AUS etc) in business area (Strategic, Consumer, etc).

However, I want each measure when market = all then add the value to each market (UK, DE,AUS, etc) as I have a visual that is calculating the number of complete over the markets and I cannot have ALL market into my table visual, as I need to compare which markets have completed the most.  

So when market equals all, add that value to each portfolio's value. For instance there are 2 tasks completed in the plan project stage for business strategic area  , however, there are 3 DE tasks in plan in the same area so I want it it to show there has been 5 completed for DE for plan just for strategic, etc.  

Market	Business Area	Projects	Phase	Status	Actual Date	Expected Date
All	Analytics	Task1	Plan	In Progress	01/01/2022	01/01/2022
All	Analytics	Task2	Initiate	In Progress	02/01/2022	02/01/2022
All	Analytics	Task3	Initiate	In Progress	03/01/2022	03/01/2022
All	Analytics	Task4	Plan	In Progress	04/01/2022	04/01/2022
All	Analytics	Task5	Plan	In Progress	05/01/2022	05/01/2022
All	Analytics	Task6	Plan	In Progress	06/01/2022	06/01/2022
UK	Strategic	Task7	Initiate	Not Started	07/01/2022	07/01/2022
UK	Strategic	Task8	Initiate	In Progress	08/01/2022	08/01/2022
UK	Strategic	Task9	Plan	In Progress	09/01/2022	09/01/2022
UK	Strategic	Task10	Go Live	Completed	10/01/2022	10/01/2022
UK	Strategic	Task11	Go Live	In Progress	11/01/2022	11/01/2022
UK	Strategic	Task12	Go Live	In Progress	12/01/2022	12/01/2022
UK	Strategic	Task13	Initiate	Completed	13/01/2022	13/01/2022
DE	Strategic	Task14	Initiate	Completed	14/01/2022	14/01/2022
DE	Strategic	Task15	Plan	Completed	15/01/2022	15/01/2022
DE	Strategic	Task16	Go Live	Completed	16/01/2022	16/01/2022
DE	Strategic	Task17	Go Live	Completed	17/01/2022	17/01/2022
DE	Strategic	Task18	Initiate	Completed	18/01/2022	18/01/2022
DE	Strategic	Task19	Initiate	Completed	19/01/2022	19/01/2022
DE	Strategic	Task20	Initiate	Completed	20/01/2022	20/01/2022
DE	Strategic	Task21	Go Live	In Progress	21/01/2022	21/01/2022
DE	Strategic	Task22	Go Live	In Progress	22/01/2022	22/01/2022
All	Transformation	Task23	Plan	Completed	23/01/2022	23/01/2022
All	Transformation	Task24	Go Live	Completed	24/01/2022	24/01/2022
All	Transformation	Task25	Initiate	Completed	25/01/2022	25/01/2022
All	Transformation	Task26	Initiate	Completed	26/01/2022	26/01/2022
All	Transformation	Task27	Plan	Completed	27/01/2022	27/01/2022
All	Transformation	Task28	Plan	Completed	28/01/2022	28/01/2022
All	Transformation	Task29	Go Live	In Progress	29/01/2022	29/01/2022
All	Transformation	Task30	Go Live	Completed	30/01/2022	30/01/2022
All	Transformation	Task31	Initiate	Completed	31/01/2022	31/01/2022
USA	Strategic	Task32	Plan	Completed	01/02/2022	01/02/2022
USA	Strategic	Task33	Initiate	Completed	02/02/2022	02/02/2022
USA	Strategic	Task34	Initiate	Completed	03/02/2022	03/02/2022
USA	Strategic	Task35	Go Live	Completed	04/02/2022	04/02/2022
USA	Strategic	Task36	Initiate	Completed	05/02/2022	05/02/2022
USA	Strategic	Task37	Go Live	Completed	06/02/2022	06/02/2022
USA	Strategic	Task38	Go Live	Completed	07/02/2022	07/02/2022
USA	Strategic	Task39	Initiate	Not Started	08/02/2022	08/02/2022
USA	Strategic	Task40	Go Live	Completed	09/02/2022	09/02/2022
All	Pharma	Task41	Plan	Completed	10/02/2022	10/02/2022
All	Pharma	Task42	Plan	Completed	11/02/2022	11/02/2022
All	Pharma	Task43	Plan	Completed	12/02/2022	12/02/2022
All	Pharma	Task44	Plan	Completed	13/02/2022	13/02/2022
All	Pharma	Task45	Initiate	Completed	14/02/2022	14/02/2022
AUS	Strategic	Task46	Initiate	Completed	15/02/2022	15/02/2022
AUS	Strategic	Task47	Plan	Completed	16/02/2022	16/02/2022
AUS	Strategic	Task48	Initiate	Completed	17/02/2022	17/02/2022
AUS	Strategic	Task49	Go Live	Not Started	18/02/2022	18/02/2022
AUS	Strategic	Task50	Go Live	Not Started	19/02/2022	19/02/2022
AUS	Strategic	Task51	Go Live	In Progress	20/02/2022	20/02/2022
AUS	Strategic	Task52	Go Live	In Progress	21/02/2022	21/02/2022
All	Strategic	Task53	Plan	Completed	22/02/2022	22/02/2022
All	Strategic	Task54	Initiate	Not Started	23/02/2022	23/02/2022
All	Strategic	Task55	Go Live	Not Started	24/02/2022	24/02/2022
All	Strategic	Task56	Initiate	Completed	25/02/2022	25/02/2022
All	Strategic	Task57	Go Live	Completed	26/02/2022	26/02/2022
All	Consumer	Task58	Plan	Completed	27/02/2022	27/02/2022
All	Consumer	Task59	Plan	Completed	28/02/2022	28/02/2022
All	Consumer	Task60	Plan	Completed	01/03/2022	01/03/2022
All	Consumer	Task61	Go Live	Completed	02/03/2022	02/03/2022
All	Consumer	Task62	Initiate	Completed	03/03/2022	03/03/2022
All	Consumer	Task63	Plan	Completed	04/03/2022	04/03/2022
All	Consumer	Task64	Initiate	Completed	05/03/2022	05/03/2022
All	Consumer	Task65	Initiate	Completed	06/03/2022	06/03/2022
All	Consumer	Task66	Initiate	Completed	07/03/2022	07/03/2022
All	Strategic	Task67	Plan	Completed	08/03/2022	08/03/2022
All	Strategic	Task68	Initiate	Completed	09/03/2022	09/03/2022

[Anonymous]

Hi  @knowledgesearch

You can try the following dax.

Create calculated table.

Table =
FILTER(
    'Table','Table'[Market]="All"&&'Table'[Status]="Completed")

Create measure.

Flag =
var _countgroup=
COUNTX(
    FILTER(ALL('Table'),'Table'[Market]=MAX('Table'[Market])&&
    'Table'[Business Area ]=MAX('Table'[Business Area ])&&
    'Table'[Phase]=MAX('Table'[Phase])&&'Table'[Status]=MAX('Table'[Status])),[Market])
var _counall=
COUNTX(
    FILTER(ALL('Table 2'),
    [Business Area ]=MAX('Table'[Business Area ])&&[Phase]=MAX('Table'[Phase])),[Market])
return
_counall+ _countgrou

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly