SUM of Measure

[patri0t82]

Hello,

I have a measure that is providing me with a value called MTBF. The measure itself is called [Relative 5YR Trailing MTBF]. It is the rightmost column in the visualization below.

What I need is a CALCULATE measure that will do two things.

1) Count the number of records where [Relative 5YR Trailing MTBF] returns ">60"

2) Filters out records where 'MTBF - Data'[Maintainable Item Code] <> "Minor Issue"

I've been playing around for sometime, but I can't seem to find a way to include a filtered measure in my Calculate measure.

Any help is greatly appreciated!

[v-rongtiep-msft]

Hi @patri0t82 ，

I cannot find the "TBF - Data'[Maintainable Item Code] <> "Minor Issue" from the table.

I have created a sample, please refer to it to see if it helps you.

Create a column first.

Column = IF('MTBF - Data'[ma]=">60",1,0)

Then create a measure.

Measure 2 = CALCULATE(COUNT('MTBF - Data'[Column]),FILTER(ALL('MTBF - Data'),'MTBF - Data'[Column]=1))

If I have misunderstood your meaning, please provide your pbix file without privacy infromation and desired output.

Best Regards

Community Support Team _ Polly

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

[patri0t82]

Thank you so much for your response. Unfortunately I think I haven't been entirely clear. I'm working on a dummy file as we speak. In the meantime, I would mention that the column in that vizualization called "Relative 5YR Trailing MTBF" is actually a measure; not a column full of data.

I tried creating a custom column using the formula "  Column = IF('MTBF - Data'[Relative 5YR Trailing MTBF]="60",1,0)  " but it didn't seem to work. It finds the results for 60, however if I add a second IF statement for 15, it won't find anything. 

I'm essentially looking to place a Card that shows the count a number. Like for 60 it found 15 records. For >60 it found 28 records. etc.

MTBF - Data'[Maintainable Item Code] is another column from the MTBF-Data table that wasn't included in the vizualisation.

[v-rongtiep-msft]

Hi @patri0t82 

I have replaced the column "Relative 5YR Trailing MTBF" using a measure named "ma". Please check. There is only a value satisfy the formula you are using Column = IF('MTBF - Data'[Relative 5YR Trailing MTBF]="60",1,0)  in your table. In your description, it seems you want the value is ">60". You have only provided partial data, and I cannot get your desired output.

Could you please provide your pbix file without privacy infromation and desired output.

Best Regards

Community Support Team _ Polly

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

[patri0t82]

Once again, thank you so much for any time you've been able to spend on this. I've sent you a PM with a link to a dummy file that should make things more clear.

[v-rongtiep-msft]

Hi @patri0t82 ,

We do not have the right to view private messages, there is more information please reply in the post, thank you!

Best Regards

Community Support Team _ Polly

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

[patri0t82]

My apologies, here is the file:

https://drive.google.com/file/d/1sCjXlFG28q2sTVP_hk7lq9uZZ997AQ5D/view?usp=sharing 

[patri0t82]

Is there anybody who would mind taking a look at this file for me? I was about to give up hope but figured I should ask just one more time.

Thanks again,

[patri0t82]

I feel like I've made some progress thanks to another post here in the forums, however my number is incorrect.

KPI - Count Failures - Motor - FinFan = 
VAR _1 =
    CALCULATE(
        COUNTROWS ('MTBF - Data'),
        FILTER ( VALUES ( 'MTBF - Data'[Equipment ID] ), [Relative 5YR Trailing MTBF] = 60),
        FILTER ( 'MTBF - Data', [Maintainable Item Code] <> "Minor Issue"),
        FILTER ( 'Asset List (Master)', [KPI Category] = "MOTOR - FINFAN")
    )
VAR _2 =
    IF ( ISBLANK ( _1 ), 0, _1 )
RETURN
    _2

The total count of "60" (in this case) should be 53, however it's returning 19. I have no idea where 19 is coming from. If I choose 15, I should get 44, however it returns 134.

If somebody can make sense of this code it would be greatly appreciated.