Forum Discussion

Malsha's avatar
Malsha
Icon for Helper I rankHelper I
3 years ago

Measures Issue

Hi everyone,
I have created these 3 measures for my power bi report and those 3 measures display as columns in the report..


1.   column Total Worked:

Total Worked =
CALCULATE(
    SUM('v_pbi_ccs_timesheet_data_v1'[Hours Worked]),
    'v_pbi_ccs_timesheet_data_v1'[Timesheet Job Role Name] IN {"Morning Cleaning", "Janitors", "Window Cleaner", "Duty & Morning", "Supervisor"},
    'v_pbi_ccs_timesheet_data_v1'[duration] <> "S",
    'v_pbi_ccs_timesheet_data_v1'[duration] <> "H"
)

2. column Allocated: 
worked hours for selected period =
var start_date = MIN(v_pbi_ccs_timesheet_data_v1[timesheet_date])
var end_date = MAX(v_pbi_ccs_timesheet_data_v1[timesheet_date])
var ans = DATEDIFF(start_date,end_date,DAY) + 1
var hours = SUM(v_pbi_ccs_subunit_v1[allocated_hours])
return
(ans * hours)/7

3. column Difference:
difference = [Total Worked] - [worked hours for selected period]

If total worked column has blank values, I want to show the value as "N/A" in the difference column. How can I do this?

Need Help

5 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    3 years ago

    Hi Malsha 

    difference =if(isblank( [Total Worked]),"N/A", [Total Worked] - [worked hours for selected period])

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

    • Malsha's avatar
      Malsha
      Icon for Helper I rankHelper I
      3 years ago

      Hi, This is working but it gives duplicated records. what's the reason for that.

  • BITomS's avatar
    BITomS
    Icon for Solution Supplier rankSolution Supplier
    3 years ago

    Hi Malsha 

     

    For your 3rd measure, I think you can use an IF expression with ISBLANK:

     

    Measure = IF(ISBLANK([Total Worked]),"N/A",[Total Worked] - [worked hours for selected period])

     

    Hope this helps.

    • Malsha's avatar
      Malsha
      Icon for Helper I rankHelper I
      3 years ago

      Hi, This works but it gives duplicated records.