Forum Discussion
Anonymous
5 years agoNot applicable
Calculating value differences for each week
I have some 'availability' numbers (a percentage) for a bunch of machines on a weekly basis. My raw CSV data looks like this: Machine,WW,Availability
A,WW35,0.9
B,WW35,0.95
C,WW35,1
D,WW35,0....
mahoneypat
5 years agoMicrosoft Employee
Here is one way to do this one to get the result below.
First you need to add a column in query or with a DAX column to get the weeknumber as an integer. You can then use these measure expressions (they differ only in the Return part). The IF in the Return of the New Pass measure is to prevent a result of 2 showing in WW35.
New Pass =
VAR thisweek =
MAX ( Availability[WeekNumber] )
VAR summary =
ADDCOLUMNS (
VALUES ( Availability[Machine] ),
"@ThisWeek",
CALCULATE (
COUNT ( Availability[Machine] ),
Availability[Availability] > 0.9,
Availability[WeekNumber] = thisweek
) + 0,
"@LastWeek",
CALCULATE (
COUNT ( Availability[Machine] ),
Availability[Availability] > 0.9,
ALL (
Availability[WeekNumber],
Availability[WorkWeek]
),
Availability[WeekNumber] = thisweek - 1
) + 0
)
RETURN
IF (
thisweek
= CALCULATE (
MIN ( Availability[WeekNumber] ),
ALL ( Availability )
),
BLANK (),
COUNTROWS (
FILTER (
summary,
[@ThisWeek] - [@LastWeek] = 1
)
)
)
New Fail =
VAR thisweek =
MAX ( Availability[WeekNumber] )
VAR summary =
ADDCOLUMNS (
VALUES ( Availability[Machine] ),
"@ThisWeek",
CALCULATE (
COUNT ( Availability[Machine] ),
Availability[Availability] > 0.9,
Availability[WeekNumber] = thisweek
) + 0,
"@LastWeek",
CALCULATE (
COUNT ( Availability[Machine] ),
Availability[Availability] > 0.9,
ALL (
Availability[WeekNumber],
Availability[WorkWeek]
),
Availability[WeekNumber] = thisweek - 1
) + 0
)
RETURN
COUNTROWS (
FILTER (
summary,
[@ThisWeek] - [@LastWeek] = -1
)
)
Steady =
VAR thisweek =
MAX ( Availability[WeekNumber] )
VAR summary =
ADDCOLUMNS (
VALUES ( Availability[Machine] ),
"@ThisWeek",
CALCULATE (
COUNT ( Availability[Machine] ),
Availability[Availability] > 0.9,
Availability[WeekNumber] = thisweek
) + 0,
"@LastWeek",
CALCULATE (
COUNT ( Availability[Machine] ),
Availability[Availability] > 0.9,
ALL (
Availability[WeekNumber],
Availability[WorkWeek]
),
Availability[WeekNumber] = thisweek - 1
) + 0
)
RETURN
COUNTROWS (
FILTER (
summary,
[@ThisWeek] - [@LastWeek] = 0
&& [@ThisWeek] = 1
)
)
Regards,
Pat
- Anonymous5 years agoNot applicable
Wow that's pretty complicated. But how do you set a measure as 'Rows' in your Matrix? Power BI won't let me do that.