Find sum of maximum values per day

[Anonymous]

I have data that look like this.

Down is number of minutes that the machine with a particular ID was down, Possible is a number of minutes in a day that that a machine can be up and running. The sum of down minutes for a single machine per day cannot be bigger than the number of Possible minutes (1440 in this case). The number of Possible minutes can vary from day to day. I need to calculate the percentage of total time that a machine (or machines) was (were) down for a particular time period which I will show as a KPI.

For example, machine with ID 801 was down for these 7 days for a total of 119 minutes out of 7*1440 (or 1.18%). Out of these 119 down minutes, 114 was for P1 Problem (1.13%), and 5 was for P3 Problem (0.05%)

Then I need to create a bar chart broken by Problem.

For both 801 and 802, they were down for 352 minutes out of 2*7*1440 which makes 1.75%, and so on...

I'm having trouble finding the total number of Possible minutes. I tried to sum the max Possible value for each day and ID, but couldn't make it work, especially when there are two or more machines.

[mahoneypat]

Please try this expression to get your desired results, replacing Downtime with your actual table name.

Downtime Pct = AverageX(Downtime, Downtime[Down]/Downtime[Possible])

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

// This is a VERY BAD PRACTICE but
// let's say that this table is
// all your model. For the future,
// you should create a dimensional
// model out of this and work on it.
// You'd have dimensions Dates,
// Machines, Problems. They'll be
// connected to the Fact Table T.

[% Down] =
var __dateMachine =
	SUMMARIZE(
		T,
		T[Date],
		T[Machine]
	)
var __allPossible =
	SUMX(
		// This works if 
		// you guarantee that
		// all rows in T with
		// the same machine
		// and the same date 
		// have THE SAME value in
		// [Possible]. It, of course,
		// should be TRUE but you
		// have to make sure it's
		// always TRUE.
		__dateMachine,
		CALCULATE(
			SELECTEDVALUE( T[Possible] )
		)
	)
var __allDown = SUM( T[Down] )
var __result =
	DIVIDE( __allDown, __allPossible )
return
	__result
	
// "The sum of down minutes for a single
// machine per day cannot be bigger than
// the number of Possible minutes (1440
// in this case)."
// This constraint is something that should
// be checked before your data makes it
// into the model...

[Anonymous]

Unfortunately, values in Possible column will not always be same.

[Anonymous]

@Anonymous,

How can you have the same day for the same machine many times in the table T with different values of Possible? This makes NO SENSE WHATSOEVER. For one machine and one day, the passible hours of operation MUST BE THE SAME. If that's not true, then it's logically flawed big time.

[Anonymous]

The number of Possible minutes for one machine can vary from day to day. For the same day and for the same machine, number of possible minutes is the same, but for one machine, the number of Possible minutes can vary from day to day. For example, something like this.

[Anonymous]

You did not understand what I was requiring in the measure. Please go back to it and read it carefully. My measure IS CORRECT and the requirement I stated in there MUST always be TRUE. You have just confirmed it is TRUE.

[Anonymous]

First of all, thank you all for your help, I am new to PBI, so please don't get mad for my mistakes. 🙂

You are correct daxer, my bad, the KPI I needed is correct.

The second part I think is not working. Let me explain.

For this machine and for these 7 days, we have total of 119 minutes it was down out of 7*1440 = 10080 (since for each day possilbe value was 1440). This makes 1.18% of time it was down. This is the correct KPI I get with your measure.

For these 1.18% for MachineID 801, 114 was for P1 problem, and 5 was for P3 problem.

1.18 * (114 / 119) = 1.13

1.18 * (5 / 119) = 0.05

And I need this.

Instead, I get this.

[Anonymous]

@Anonymous

For this type of analysis, you HAVE to create a proper dimensional model with at least a separate Date table (but I'd suggest to do it right and create 2 more dimensions: Machine and Problem). You will not be able to carry out such calculations in your current naïve model that consists of just 1 table. I can explain later why this is so.

[Anonymous]

// Assumptions:
// 1. Dates - dim that is the Date table in the model.
// 2. Machines - dim that stores machines.
// 3. Problem - dim that stores types of problems.
// 4. Stats - fact table that you've pasted.
// Relationships:
// 1. Stats * <-one-way- 1 Dates on [Date]
// 2. Stats * <-one-way- 1 Machines on [MachineID]
// 3. Stats * <-one-way- 1 Problems on [ProblemID]
// All fields in the fact table must be hidden.
// Only measures can be visible in there.
// Please note that if you deviate from this dimensional
// design, you may encounter bugs and strange behaviours
// that you will not be able to understand. So, please
// for your own good, stick to the PROPER DIM DESIGN.

// Say, you've selected some machine(s),
// period (set of dates) and problem(s)
// from your dimensions. Here's the measure
// that gives you what you're after.
// It's simpler than before because this is
// what happens when you've got a PROPER
// DIM DESIGN. As a bonus, it works the way
// you want with ANY slicing. Slicing is 
// always done through dimensions, never
// directly on fact tables (with very, very
// rare circumstances when this is allowed).
[% Down] =
var __allPossible =
    // For all visible dates and all 
    // visible machines, sum up the
    // possible time.
    SUMX(
        SUMMARIZE(
            Stats,
            Machines[MachineID],
            Dates[Date]
        ),
        CALCULATE(
            SELECTEDVALUE( Stats[Possible] )
        )
    )
var __allDown = SUM( Stats[Down] )
var __result =
    DIVIDE( __allDown, __allPossible )
return
    __result

[Anonymous]

Hi daxer, first of all thanks for your time and help.

I did as you told me.

% Down = 
VAR __allPossible =
    SUMX(
        SUMMARIZE(
                'SS FACT',
                'SS DIM ID'[MachineID],
                'SS DIM DATE'[Date]
        ),
            CALCULATE(
                    SELECTEDVALUE('SS FACT'[Possible])))

VAR __allDown = SUM('SS FACT'[Down])
VAR __result = DIVIDE(__allDown, __allPossible)
RETURN __result

And still I get wrong number (0.35%) on the horizontal bar chart. The KPI is ok.

[Anonymous · Accepted solution]

OK, this one should be correct now (but test it well with different selections on your dimensions):

[% Down] =
var __allPossible =
    // For all visible dates and all 
    // visible machines, sum up the
    // possible time.
    CALCULATE(
        SUMX(
            SUMMARIZE(
                Stats,
                Machines[MachineID],
                Dates[Date]
            ),
            CALCULATE(
                SELECTEDVALUE( Stats[Possible] )
            )
        ),
        ALLEXCEPT(
            Stats,
            Machines,
            Dates
        )
    )
var __allDown = SUM( Stats[Down] )
var __result =
    DIVIDE( __allDown, __allPossible )
return
    __result

[Anonymous]

Please remember to only expose measures in your Stats table (fact table). All columns in this table should be hidden. Slicing only through dimensions. VERY IMPORTANT.

[Anonymous]

Works like a charm. Thank you daxer.

[Anonymous]

Sorry, I can't use priv messages for some reason 😞

"what do you exactly mean when you say that columns in fact table should be hidden"

I mean that all columns in the table should be hidden from view, nothing more, nothing less. There's a setting on each column (right-click and you'll see it) that lets you hide the column. Users should not be able to drop the column(s) on the canvas.