Calculate the result of a measure

[bibo]

Hi , 

I am trying to count the result of a measure that i have created from a table ,

The table shows maintenance activity of different tools with a lot of maintenance notifications activities in the last two years.

i created a measure to indicate the equipment status whether it is in Repair or Pending Upgrade or Ready .

The table shows Maintenance Activity Versus Equipment Number and if the Notification is completed or not , Note : Same Equipment Number can have multiple maintenance activities

i used the following measure ;

IF(CALCULATE(COUNTBLANK('Maintenance Notifications'[Completion date]),'Maintenance Notifications'[Notification Type]="Failure")>0,"Repair",IF(CALCULATE(COUNTBLANK('Maintenance Notifications'[Completion date]),'Maintenance Notifications'[Notification Type]="Upgrade")>0,"Pending Upgrade","READY"))

it works fine to show the status of the tool when i put it in a table visualization but next step i am trying to calculate how many tools are Ready , in Repair and Pending Upgrade . I tried different ways to Count the result of the measure without any luck .

i appreciate your help and feedback .

[v-rzhou-msft]

Hi @bibo 

I build a table like yours to have a test.

Maintenance Notifications Table:

Firstly I use your measure to get a similar table visual like yours

Then I build three measures to achieve your goal：

Upgrade = 
CALCULATE (
    COUNTROWS ( 'Maintenance Notifications' ),
        'Maintenance Notifications'[Notification Type] = "Upgrade"
)

Failure = 
CALCULATE (
    COUNTROWS ( 'Maintenance Notifications' ),
        'Maintenance Notifications'[Notification Type] = "Failure"
)

Color Value = 
SWITCH ( [Statue], "READY", 0, "Pending Upgrade", 1, "Repair", 2 )

Then I build another table visual：

Use Icon in Conditional formatting：

Result：

You can download the pbix file from this link:Case_Calculate the result of a measure.pbix 

Best Regards,

Rico Zhou

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

[bibo]

@RicoZhou

Thanks a lot for the reply and the Effort . 

i downloaded the file but when i use the measure "Upgrade" to count rows in the table , it will give number 6 based on the counting in " Notification Type " and  i am not able to create a measure to count the status in the "Statue" since it's a measure

if i created a column in the table to give me the same value of 'Statue' measure , you will end up having a tools like tool #5 appears in two counts ( Ready and Pending Upgrade ) and the reality that it has to be counted only in the "Pending Upgrade" and cannot be considered to be ready .

for the tools in Upgrade state; it's two based on the table below . and that's what i am trying to achieve to have the number of tools in Upgrade , Repair and Ready

[Greg_Deckler]

@bibo - This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

Obviously COUNTX would also work. Bottom line, create your table, use one of the "X" aggregation functions to iterate over it. 

Also, seems like you might like my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

Also, if you are looking at seeing status by intervals, this might help as well. 
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

[bibo]

Hello @Greg_Deckler  , Thanks for the reply . I tried to follow the steps but still was not successful .

Note : Thanks a lot for pointing out to the MTBF article , it's very useful .

[amitchandak]

@bibo ,Can you share sample data and sample output in table format?

[bibo]

Hello @amitchandak , 

here is a sample of the data that i use to build my visualizations ;

I used to build the following Visual using the Equation that i mentioned in my previous post , Please note that some of the Equipment can have more than one maintenance notifications ( some of them are completed while others can be opened )

I am trying to ;

1. Build a visualization to show how many tools in Green " READY " , Red " REPAIR" , Yellow " Pending Upgrade"
2. Having a slicer to filter the tools in the visaulization based on the status .

[Ashish_Mathur]

Hi,

Based on the sample data that you have shared, show the expected result.  Also, share data in a format that can be pasted in an Excel workbook.