Help with a measure or calculation to use

[DP2022]

Hi team

I have a list of companies which contains data on what their organisation headcount is and their turnover. The columns contain the following inputted information in those columns:

Column 1

Company X

Company Y 

Company Z

Each company will select one of the following and will appear in Column 2

Micro: 1-9
Small: 10-49
Medium: 50-499
Large: 500+

Each company will select one of the following and will appear in Column 3
£1 - £1,700,000
£1,700,001 - £8,200,000
£8,200,001 - £41,000,000
£41,000,001+
Not Applicable

I'm trying to use a measure/calculation which looks at the response of each company and calculates the definition of an SME which is any company which is less than 499 employees and turnover less than £41,000,000. So in Column 4 I would get an output stating "SME" or an output "Not defined as an SME". 
This will allow me to get to a percentage number of companies in the list which are defined as SME. 

Another measure / calculation I want to create is to apply the above but only on unique company entries in Column 1. Column 1 will contain duplicate company name entries so I'm looking to have the measure above on all companies regardless of duplicate entries and another measure just looking at the  percentage number of companies in the list which are defined as SME (discounting duplicate company name entries)

Is anyone able to provide help?

Thanks in advance. 

D

[PaulDBrown]

Try:

SME? =
IF (
    AND (
        MAX ( Table[Column 2] ) <> "Large: 500+",
        MAX ( Table[Column 3] )
            IN { "£1 - £1,700,000", "£1,700,001 - £8,200,000", "£8,200,001 - £41,000,000" }
    ),
    "SME",
    "Not defined as SME"
)

For the Unique company calculation, try

% SME =
VAR _SME =
    CALCULATE (
        DISTINCTCOUNT ( Table[Column 1] ),
        FILTER ( Table, [SME?] = "SME" )
    )
VAR _AC =
    DISTINCTCOUNT ( Table[Column 1] )
RETURN
    DIVIDE ( _SME, _AC )

Having said that, it is probably worth  creating a calculated column in the actual table which will enable you to filter for SMEs or otherwise. If so, use this code for the calculated column:

SME? =
IF (
    AND (
        Table[Column 2] <> "Large: 500+",
        Table[Column 3]
            IN { "£1 - £1,700,000", "£1,700,001 - £8,200,000", "£8,200,001 - £41,000,000" }
    ),
    "SME",
    "Not defined as SME"
)

[DP2022]

Hi Paul

Thanks for getting back to me. I've just had a chance to look at your response and apply it to the power bi file I'm working on.

With the first measure you listed and this part: MAX ( Table[Column 2] ) <> "Large: 500+",

Is that only searching for responses which have inputted "Large: 500+"?

An SME would be a company that's either micro, small or medium, so should that part include the following:

"Micro: 1-9", "Small: 10-49", "Medium:50-499".

With the last part: 

"SME",
"Not defined as SME"
)

Will that only bring up entries not defined as SME? I need the measure to bring up a calculation that only includes SMEs i.e. micro, small and medium and in the following turnover ranges; "£1 - £1,700,000", "£1,700,001 - £8,200,000", "£8,200,001 - £41,000,000" 

Thanks again for looking at this. 

SME? =
IF (
    AND (
        MAX ( Table[Column 2] ) <> "Large: 500+",
        MAX ( Table[Column 3] )
            IN { "£1 - £1,700,000", "£1,700,001 - £8,200,000", "£8,200,001 - £41,000,000" }
    ),
    "SME",
    "Not defined as SME"
)