Dax Forumula using power bi

[EricoVincentciu]

i wanna make some calculation like this : 

if i filter like this images

count calculation like this

if i filter this one

count calculation like this, 

if i filter the dept and type total utilization will be interactive, and i wanna make it real time if it possible   

Flat file relation :

real time table i have : 

if filtered current date :

active time, down time and not used keep mooving, cause this is real time data

and i wanna ask relation regarding table fuel utillization and reg

 Data 

i Wanna Make some total utillization like that

so far, i already make it using power bi 

this is flat file (not real time)

the yellow mark, already correct but the red one still wrong, i wanna convert it to percentage

SELECT vm.internal_id, 
	   vm.seq, 
	   vm.start_date_vhl_down AS start_date, 
	   vm.finish_date_vhl_down AS finish_date, 
	   vm.type AS trx, 
	   '' AS shift, 
	   vm.start_time_vhl_down AS start_time, 
	   vm.finish_time_vhl_down AS finish_time, 
	   '' AS duration, 
	   CONCAT(COALESCE(vm.start_vhl_down_note, ''), ';', COALESCE(vm.finish_vhl_down_note, '')) AS note, 
	   vm.status AS vehicle_status, 
	   vm.dept_id, 
	   vmd.value, 
	   vmd.detail, 
	   vr.vehicle_id, 
	   vr.type, 
	   vr.sub_type, 
	   vr.owned, 
	   vr.rent, 
	   vr.id_type, 
	   vt.id_type AS id_type_2 
FROM vms_vhl_maintenance AS vm 
JOIN vms_vhl_mst_dept AS vmd ON vmd.id = vm.dept_id 
JOIN vms_vhl_reg AS vr ON vr.internal_id = vm.internal_id 
JOIN vms_vhl_mst_type AS vt ON vt.id = vr.id_type 
WHERE 1 = 1 
--AND vm.internal_id = 'DTOF000004' 
--AND vm.start_date_vhl_down >= '2024-02-01' 
--AND (vm.finish_date_vhl_down IS NULL OR vm.finish_date_vhl_down <= '2024-02-29') 
AND vm.status = 'C';

SELECT vo.internal_id, 
	   vo.operational_date AS start_date, 
	   vo.operational_date AS finish_date, 
	   'OPERATIONAL' AS trx, 
	   '' AS shift, 
	   vo.time_start AS start_time, 
	   vo.time_finish AS finish_time, 
	   '' AS duration, 
	   vo.note, 
	   vo.status AS vehicle_status, 
	   vo.dept_id,
	   vr.vehicle_id, 
	   vr.id_type, 
	   vr.type, 
	   vr.sub_type, 
	   vr.owned, 
	   vr.rent, 
	   vmd.value, 
	   vmd.detail, 
	   vt.id_type AS id_type_2 
FROM vms_vhl_operational AS vo 
JOIN vms_vhl_mst_dept AS vmd ON vmd.id = vo.dept_id 
JOIN vms_vhl_reg AS vr ON vr.internal_id = vo.internal_id 
JOIN vms_vhl_mst_type AS vt ON vt.id = vr.id_type 
WHERE 1 = 1 
--AND vo.internal_id = 'DTOF000004' 
--AND vo.operational_date BETWEEN '2024-02-01' AND '2024-02-29' 
AND vo.status = 'C';

--/STEP 2/ (Crossing date )
SELECT vm.internal_id, 
	   vm.seq, 
	   vm.start_date_vhl_down AS start_date, 
	   vm.finish_date_vhl_down AS finish_date, 
	   vm.type AS trx, 
	   '' AS shift, 
	   vm.start_time_vhl_down AS start_time, 
	   vm.finish_time_vhl_down AS finish_time, 
	   '' AS duration, 
	   CONCAT(COALESCE(vm.start_vhl_down_note, ''), ';', COALESCE(vm.finish_vhl_down_note, '')) AS note, 
	   vm.status AS vehicle_status, 
	   vm.dept_id, 
	   vmd.value, 
	   vmd.detail, 
	   vr.vehicle_id, 
	   vr.type, 
	   vr.sub_type, 
	   vr.owned, 
	   vr.rent, 
	   vr.id_type, 
	   vt.id_type AS id_type_2 
FROM vms_vhl_maintenance AS vm 
JOIN vms_vhl_mst_dept AS vmd ON vmd.id = vm.dept_id 
JOIN vms_vhl_reg AS vr ON vr.internal_id = vm.internal_id 
JOIN vms_vhl_mst_type AS vt ON vt.id = vr.id_type 
WHERE 1 = 1 
AND MONTH(vm.start_date_vhl_down) <> MONTH(vm.finish_date_vhl_down) 
--AND vm.internal_id = 'DTOF000004' 
--AND ( vm.start_date_vhl_down BETWEEN '2024-02-01' AND '2024-02-29' ) 
AND vm.status = 'C';

SELECT vm.internal_id, 
	   vm.seq, 
	   vm.start_date_vhl_down AS start_date, 
	   vm.finish_date_vhl_down AS finish_date, 
	   vm.type AS trx, 
	   '' AS shift, 
	   vm.start_time_vhl_down AS start_time, 
	   vm.finish_time_vhl_down AS finish_time, 
	   '' AS duration, 
	   CONCAT(COALESCE(vm.start_vhl_down_note, ''), ';', COALESCE(vm.finish_vhl_down_note, '')) AS note, 
	   vm.status AS vehicle_status, 
	   vm.dept_id, 
	   vmd.value, 
	   vmd.detail, 
	   vr.vehicle_id, 
	   vr.type, 
	   vr.sub_type, 
	   vr.owned, 
	   vr.rent, 
	   vr.id_type, 
	   vt.id_type AS id_type_2 
FROM vms_vhl_maintenance AS vm 
JOIN vms_vhl_mst_dept AS vmd ON vmd.id = vm.dept_id 
JOIN vms_vhl_reg AS vr ON vr.internal_id = vm.internal_id 
JOIN vms_vhl_mst_type AS vt ON vt.id = vr.id_type 
WHERE 1 = 1 
AND MONTH(vm.start_date_vhl_down) <> MONTH(vm.finish_date_vhl_down) 
--AND vm.internal_id = 'DTOF000004' 
--AND ( vm.finish_date_vhl_down BETWEEN '2024-02-01' AND '2024-02-29' ) 
AND vm.status = 'C';

SELECT vm.internal_id, 
	   vm.seq, 
	   vm.start_date_vhl_down AS start_date, 
	   vm.finish_date_vhl_down AS finish_date, 
	   vm.type AS trx, 
	   '' AS shift, 
	   vm.start_time_vhl_down AS start_time, 
	   vm.finish_time_vhl_down AS finish_time, 
	   '' AS duration, 
	   CONCAT(COALESCE(vm.start_vhl_down_note, ''), ';', COALESCE(vm.finish_vhl_down_note, '')) AS note, 
	   vm.status AS vehicle_status, 
	   vm.dept_id, 
	   vmd.value, 
	   vmd.detail, 
	   vr.vehicle_id, 
	   vr.type, 
	   vr.sub_type, 
	   vr.owned, 
	   vr.rent, 
	   vr.id_type, 
	   vt.id_type AS id_type_2 
FROM vms_vhl_maintenance AS vm 
JOIN vms_vhl_mst_dept AS vmd ON vmd.id = vm.dept_id 
JOIN vms_vhl_reg AS vr ON vr.internal_id = vm.internal_id 
JOIN vms_vhl_mst_type AS vt ON vt.id = vr.id_type 
WHERE 1 = 1 
AND MONTH(vm.start_date_vhl_down) <> MONTH(vm.finish_date_vhl_down) 
--AND vm.internal_id = 'DTOF000004' 
--AND ( vm.start_date_vhl_down < '2024-02-01' AND vm.finish_date_vhl_down > '2024-02-20' ) 
AND vm.status = 'C';

can some one help me ??

@rajendraongole1  @lbendlin  @Ritaf1983  @Kedar_Pande @SamWiseOwl @parry2k 

[samratpbi]

Hi, I took the data from sheet 2 and loaded it into PBI. 

Then I created 2 simple measuures:

cek value = SUM(cek[Value])

and 

cek value % =

DIVIDE(

    [cek value],

    CALCULATE(

        [cek value],

        ALLSELECTED(cek[Type])

    )

)

And below is the result:

Hope this helps.

If this helps to resolve your problem, then please mark it as solution provided. Thanks!

[EricoVincentciu]

thank you for your response, but i wanna ask

Can you help me to swipe this position ??

so i wanna make 100% as vertical and 0.00% as horizontal

and help me make it online (real time base on my sql code)

Data Real Time 

thankyou 

[v-linhuizh-msft · Accepted solution]

Thanks for the reply from samratpbi.

Hi @EricoVincentciu ,

Please try the following DAX:

Measure = 
VAR _sum=SUM('Table'[Value])
VAR _total=CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Type]))
RETURN
IF(_total<>0,DIVIDE(_sum,_total),0)

Result:

In addition, if you want to achieve real-time results, use Direct Query connection mode to connect to the data source you are querying instead of Import connection mode.

Best Regards,
Zhu

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

[EricoVincentciu]

haii, i wanna make some calculation like this 

if i filter departement, date, and type 

i got Active Time : 39% Downtime 0 % and not used 61 %

This calculation is interactive based on the filters

another sample is 

if i filter departement, date, and type like this

i got this  Active Time, Down time and not used

i already make it on power bi 

Power bi File 

but the result not same like what i make it 

this is my excel file : cek and sheet 2 

please help me to calculate total Active Time, Down time and not used

as percentages

thank you 

@rajendraongole1  @lbendlin  @Ritaf1983  @Kedar_Pande @SamWiseOwl @parry2k