Forum Discussion

ktt777's avatar
ktt777
Icon for Helper V rankHelper V
4 years ago
Solved

CAlculate ultilisation

hi all, 

 

i have data as below :

 

 with my number of working days in Jan is 20. How can i present a graph to show ultilisation in January: 

Vehicle A : ulilisation = (5+4)/20

Vehicle B : Ultilisation = 6/20 ... etc 

 

Overall USA ultilisation = (5+4+6)/40   

Overall Canada ultilisaton = (7+8)/40

 

thanks 

thanls 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    It is for creating a measure.

     

     

    Utilization measure: = 
VAR _workingdayscount = 20
VAR _newtable =
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[Country], Data[Vehicles] ),
        "@vehiclecount", CALCULATE ( COUNTROWS ( VALUES ( Data[Vehicles] ) ) ),
        "@workingdays", _workingdayscount,
        "@timeused", CALCULATE ( SUM ( Data[Time used] ) )
    )
RETURN
    DIVIDE ( SUMX ( _newtable, [@timeused] ), SUMX ( _newtable, [@workingdays] ) )

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    It is for creating a measure.

     

     

    Utilization measure: = 
VAR _workingdayscount = 20
VAR _newtable =
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[Country], Data[Vehicles] ),
        "@vehiclecount", CALCULATE ( COUNTROWS ( VALUES ( Data[Vehicles] ) ) ),
        "@workingdays", _workingdayscount,
        "@timeused", CALCULATE ( SUM ( Data[Time used] ) )
    )
RETURN
    DIVIDE ( SUMX ( _newtable, [@timeused] ), SUMX ( _newtable, [@workingdays] ) )
  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, ktt777 

     

    You can try the following methods.

    Measure:

    Vehicle =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[time used] ),
        ALLEXCEPT ( 'Table', 'Table'[Vehicles] )
    ),
    20
)


    Overall =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[time used] ),
        ALLEXCEPT ( 'Table', 'Table'[Country] )
    ),
    20 * CALCULATE ( DISTINCTCOUNT ( 'Table'[Country] ), ALL ( 'Table' ) )
)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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