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.
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.87
A,WW36,1
B,WW36,1
C,WW36,0.84
D,WW36,0.94
A,WW37,0.75
B,WW37,0.98
C,WW37,0.91
D,WW37,0.89
A,WW38,1
B,WW38,0.88
C,WW38,0.99
D,WW38,0.95
Data source is updated weekly and new Work Week (WW) availability data is added for each machine. A machine is deemed 'Pass' if the availability for that week is > 90%. I calculate the 'Pass' measure as below.
Pass =
VAR varCount = CALCULATE(COUNTA(data[Availability]), data[Availability] > 0.9)
RETURN
IF(varCount = BLANK(), 0, varCount)
Pass count for each machine for each week, displayed in a matrix, looks like this (given above data):
Now, I want to calculate some figures for these pass values for each machine. My actual needs are a bit complex, but few of the most basic things I wanted calculated are shown below.
To better illustrate, I put my desired results in an Excel file:
As mentioned at the beginning of the post, my source CSV is updated with new data each week, so as time goes on I will have more [WW] columns added in my PowerBI matrix. Given this I don't quite know how I can calculate the above values dynamically without hardcoding anything. Is this possible?
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
Hi @Anonymous - this might be better off doing a PowerQuery/M creation of columns showing this week's Pass/Fail and Last Week's Pass/Fail. May want to post in that forum.
@ImkeF
@vanessafvg
@tex628
@mahoneypat
@edhans
David
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |