COUNT measure assistance

[niculeica]

Hi!

I'm having some troubles trying to COUNT the number of projects, split per 3 criteria (Margin% is <0%, between 0% and 20%, then >20%). So I've created a separate table called MarginIntervals, which has a column with the 3 margin types, then there's another table, Timeline.

So, Margin% as a standalone measure works perfectly, it's calculated as (Revenue-Cost)/Revenue, and is relative to each month in Timeline table. The formula is pretty standard, a measure with declared variables (Month&Year from Timeline table as VAR Time, to be equal to Month&Year from Projects table).

Now, would you calculate MarginCount measure based on Margin% (from Projects table) and MarginTypes (from MarginIntervals table)? It feels like measure based on measure pretty much gets stuck...

So the formula is kinda like:

MarginCount =

VAR MarginTypes =

MAX(_Margins[MarginTypes])

RETURN

IF(MarginTypes="<0%",CALCULATE(COUNTA(Projects[Project: ID]),FILTER(Projects,[Margin%]<0),
IF(MarginTypes="0% - 20%",CALCULATE(COUNTA(Projects[Project: ID]),FILTER(Projects,[Margin%]>=0&&[Margin%]<=20%,
CALCULATE(COUNTA(Projects[Project: ID]),FILTER(Projects,[Margin%]>20%)))

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Anonymous · Accepted solution]

Hi @niculeica ,

Based on your description I created simple test data.

MarginIntervals:

Projects:

Timeline:

You can use the SWITCH function and create a slicer to accomplish your needs. Please follow the steps:

1.Create the relationship:

2.Create a new column:

Margin% = DIVIDE('Projects'[Revenue]-'Projects'[Cost],'Projects'[Revenue])

3.Create a measure:

MarginCount = 
SWITCH(
    SELECTEDVALUE(MarginIntervals[MarginTypes]),
    "<0%",CALCULATE(COUNTA('Projects'[ProjectID]),FILTER(Projects, 'Projects'[Margin%] < 0)),
    "0% - 20%",CALCULATE(COUNTA('Projects'[ProjectID]),FILTER(Projects, 'Projects'[Margin%] >= 0 && 'Projects'[Margin%] <= 0.2)),
    ">20%",CALCULATE(COUNTA(Projects[ProjectID]),FILTER(Projects, 'Projects'[Margin%] > 0.2)),
    BLANK()
)

4.The result is as follows:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.