Add Value of Each Measure When the Values are a Specific Number

[kylerwimbush]

I have 17 measures that will out put a value 0-3 depending on the value of a row in a column.

Measure 1 = If (Column = 100, "3", If (Column = 99, "2", If (Column = 98, "1", If (Column <= 97, "0", "Other")))) 

What I want to do is create a sum measure that will add up only the measures that have an output of three. So if all 17 measures had an output of 3, the sum measure would show a value of 51. If only 13/17 measures had an output of 3 the value would be 39. 

I cant seem to find a way to do it though. 

I eventually want to create 2 more measures like this but for when the value of each measure is 2, and if the value of each measure is 1. I assume the syntax will be the same. Any help would be appreciated, thanks. 

[kylerwimbush · Accepted solution]

2 Part Answer 

1st Part: FirstMeasure = If([Measure1] = 3,3)+If([Measure2] = 3,3)...

2nd Part: SecondMeasure = If([FirstMeasure] = 0, "Other", [FirstMeasure]) 

Not my Solution! Credit goes to @DavidLR 

[kylerwimbush · Accepted solution]

2 Part Answer 

1st Part: FirstMeasure = If([Measure1] = 3,3)+If([Measure2] = 3,3)...

2nd Part: SecondMeasure = If([FirstMeasure] = 0, "Other", [FirstMeasure]) 

Not my Solution! Credit goes to @DavidLR 

[DavidLR · Accepted solution]

Thanks @kylerwimbush! 

[Ashish_Mathur]

Hi,

The very fact that you have written 17 measures seems to suggest that the data structure is sub optimal.  Share some data, describe the question and show the expected result.

[kylerwimbush]

The reason I have 17 measures is because each measure has different parameters that decide what will make the number 0, 1, 2, or 3. I have 17 columns that each need their own measure. 

So one measure is like,

Measure 1 = If (Column = 100, "3", If (Column = 99, "2", If (Column = 98, "1", If (Column <= 97, "0", "Other")))) 

then the other(s) is like, 

Measure 2 = If (Column2 = 80, "3", If (Column2 = 89, "2", If (Column = 88, "1", If (Column2 <= 87, "0", "Other")))).

Unfortunatley, I can't show my data because it is sensitive information. 

[v-yanjiang-msft]

Hi @kylerwimbush ,

According to your description, here's my solution.

1.Create a calculated column. Calculated column is more convenient to show the output with one measure then.

Result =
IF (
    [Column] = 100,
    "3",
    IF (
        [Column] = 99,
        "2",
        IF ( [Column] = 98, "1", IF ( [Column] <= 97, "0", "Other" ) )
    )
)

2.Create a measure.

Measure =
IF (
    MAX ( 'Table'[Result] ) = "Other",
    BLANK (),
    MAX ( 'Table'[Result] ) * COUNTROWS ( 'Table' )
)

Then put the calculated column and the measure in a visual, get the result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[kylerwimbush]

Sorry I should have mentioned that I have 17 measures because I have 17 columns that require their own measure. I believe that this method would require me to create 17 calculated columns. I would like to stick to keeping my measures because they work well and other metrics in my report rely on them to function properly.

I am just looking for an easy way to filter for the number 3 across all measures, return a count for how many 3s there are, and then multiply that count by 3. Its probably easier said than done.