Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I've got a dataset (representative of a work-related problem but not real data) as per images below: a Plans table and a ServiceRecord table, related by PlanID. I developed a measure (PlanHoursThisPeriod) which broke out the high-level plan hours according to the period context on the pivot. The PlanHoursThisPeriod2 measure is an attempt to get this measure to work on row totals, but I've run out of ideas. In the image showing the pivot, instead of the (red) 45.9 on the Jan 2018 row, it should show the sum of 15.3 and 8.6 (green cells).
I'm sure someone can see an easier way of doing this, but my PlanHoursThisPeriod2 measure so far is below:
=VAR FirstDateOfPeriod = FIRSTDATE('Calendar'[Date Key])
VAR LastDateOfPeriod = LASTDATE('Calendar'[Date Key])
VAR PlanStartDate = MAX(Plans[StartDate])
VAR PlanEndDate = MAX(Plans[EndDate])
VAR DailyPlanHours = MAX(Plans[DailyPlanHours])
RETURN
IF(COUNTROWS(VALUES('Calendar'[Year]))>1,MAX(Plans[PlanHours]),
// This block below is trying to get row totals to work.
IF(COUNTROWS(VALUES(Plans[PlanID]))>1,
SUMX(Plans,
(IF(FirstDateOfPeriod>PlanEndDate,BLANK(),
IF(LastDateOfPeriod<PlanStartDate,BLANK(),
IF(AND(LastDateOfPeriod<=PlanEndDate,FirstDateOfPeriod>=PlanStartDate),
DailyPlanHours*[DaysInPeriod],
IF(AND(FirstDateOfPeriod>=PlanStartDate,PlanEndDate<=LastDateOfPeriod),
DailyPlanHours*(PlanEndDate-FirstDateOfPeriod+1),
IF(AND(FirstDateOfPeriod<=PlanStartDate,LastDateOfPeriod<=PlanEndDate),
DailyPlanHours*(LastDateOfPeriod-PlanStartDate)))))))) ,
// block above for row totals
IF(FirstDateOfPeriod>PlanEndDate,BLANK(),
IF(LastDateOfPeriod<PlanStartDate,BLANK(),
IF(AND(LastDateOfPeriod<=PlanEndDate,FirstDateOfPeriod>=PlanStartDate),
DailyPlanHours*[DaysInPeriod],
IF(AND(FirstDateOfPeriod>=PlanStartDate,PlanEndDate<=LastDateOfPeriod),
DailyPlanHours*(PlanEndDate-FirstDateOfPeriod+1),
IF(AND(FirstDateOfPeriod<=PlanStartDate,LastDateOfPeriod<=PlanEndDate),
DailyPlanHours*(LastDateOfPeriod-PlanStartDate+1))))))))
Take a look at this post and see if it helps.
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |