Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ktt777
Helper V
Helper V

CAlculate ultilisation

hi all, 

 

i have data as below :

 

ktt777_0-1657459464803.png

 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 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Untitled.png

 

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] ) )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @ktt777 

 

You can try the following methods.

Measure:

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

vzhangti_1-1657675833663.png

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

vzhangti_2-1657675879466.png

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.

Jihwan_Kim
Super User
Super User

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.

 

Untitled.png

 

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] ) )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.