Forum Discussion

jps_HHH's avatar
jps_HHH
Helper II
2 years ago
Solved

create a measure to count using data from two excel tabs

I have a excel database linked to PowerBI file. 

The excel file contains two tabs: 

     1 - List of maintenances performed (each line is a maintenance to a specifc equipment. the maintenance is repeat at every certain frequency) 

     2 - List of failures detected in each maintenance/equipment 

 

The number of failures detected is unpredictable. One equipment can have 0 failures or 20. 

The ratio of this two excel tabs is not 1:1.

 

I would like to create a measure in the powerBI similar to:   "count the number of fails per equipment / number of maintenances performed to that equipment.

 

Is that possible ?    

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi jps_HHH ,
    Thanks for AmiraBedh reply.
    Here some steps that I want to share, you can check them if they suitable for your requirement.
    Here is my test data:

    Create relationships

    Create measures

    Count of maintenance times = 
CALCULATE(
    COUNT('maintenances performed'[machine id]),
    ALLEXCEPT(
        'maintenances performed',
        'maintenances performed'[machine id]
    )
)
    Sum of failure times = 
CALCULATE(
    SUM('failures detected'[machine failure times]),
    ALLEXCEPT(
        'maintenances performed',
        'maintenances performed'[machine id]
    )
)
    Measure = [Sum of failure times]/[Count of maintenance times]

    Final output

     

    Best regards,
    Albert He

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

     

     

     



2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi jps_HHH ,
    Thanks for AmiraBedh reply.
    Here some steps that I want to share, you can check them if they suitable for your requirement.
    Here is my test data:

    Create relationships

    Create measures

    Count of maintenance times = 
CALCULATE(
    COUNT('maintenances performed'[machine id]),
    ALLEXCEPT(
        'maintenances performed',
        'maintenances performed'[machine id]
    )
)
    Sum of failure times = 
CALCULATE(
    SUM('failures detected'[machine failure times]),
    ALLEXCEPT(
        'maintenances performed',
        'maintenances performed'[machine id]
    )
)
    Measure = [Sum of failure times]/[Count of maintenance times]

    Final output

     

    Best regards,
    Albert He

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

     

     

     



  • AmiraBedh's avatar
    AmiraBedh
    Super User
    2 years ago

    Can you share your datamodel ?

    Based on what you described I am assuming the following :

    MaintenancesCount = COUNTROWS('MaintenanceTable')

FailuresCount = COUNTROWS('FailuresTable')

FailuresPerMaintenance = 
DIVIDE(
    CALCULATE(COUNTROWS('FailuresTable'), 'FailuresTable'[EquipmentID]),
    CALCULATE(COUNTROWS('MaintenanceTable'), 'MaintenanceTable'[EquipmentID]),
    0
)