Forum Discussion

niculeica's avatar
niculeica
Icon for Helper I rankHelper I
1 year ago
Solved

COUNT measure assistance

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%)))
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

     

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    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.