Forum Discussion

p0nk's avatar
p0nk
Frequent Visitor
7 years ago
Solved

Compare value against target range for multiple entries

Hello,

 

I currently have the following table within Power BI:

The blue columns are the categories that I would like to use, the red column is the count of each entry for the repective category and the % of distribution is a measure to find the percentage of the total for each category.

 

I would like to have another column that that if category 1 in % of distribution is between 0 and 0.05 = "No" otherwise "Yes", is category 2 in % of distrbution is between 0.7 and 0.9 = "No" otherwise "Yes" and if category 3 in % of distribution is between 0.1 and 0.2 = "No" otherwise "Yes". 

 

Ideally I would like the solution to be in the form of a measure if possible but other solutions are welcome.

 

Any help would be much appreciated,

Thanks.

Need Help
  • TeigeGao's avatar
    TeigeGao
    7 years ago

    Hi p0nk ,

    We can create a measure like below:

    Measure =
IF (
    MIN ( Table1[Category] ) = "A",
    IF ( [% of distribution] >= 0 && [% of distribution] <= 0.05, "No", "Yes" ),
    IF (
        MIN ( Table1[Category] ) = "B",
        IF ( [% of distribution] >= 0.7 && [% of distribution] <= 0.9, "No", "Yes" ),
        IF ( [% of distribution] >= 0.1 && [% of distribution] <= 0.2, "No", "Yes" )
    )
)

    The result will like below:

    Best Regards,

    Teige

1 Reply

  • TeigeGao's avatar
    TeigeGao
    Solution Sage
    7 years ago

    Hi p0nk ,

    We can create a measure like below:

    Measure =
IF (
    MIN ( Table1[Category] ) = "A",
    IF ( [% of distribution] >= 0 && [% of distribution] <= 0.05, "No", "Yes" ),
    IF (
        MIN ( Table1[Category] ) = "B",
        IF ( [% of distribution] >= 0.7 && [% of distribution] <= 0.9, "No", "Yes" ),
        IF ( [% of distribution] >= 0.1 && [% of distribution] <= 0.2, "No", "Yes" )
    )
)

    The result will like below:

    Best Regards,

    Teige