Summariza a column that is not affected by some filters

[andreazambon]

Hi all, 

Today i have to reach something tricky, if you are skilled with lean concept of availability, OEE etc. you will understand my issue easier:

I have a table with events, weeks, machines, durations, and other fields. An easy example:

Machine	Week	Event	Type_event	Minutes
10	25	PRODUCTION	PRODUCTION	30
10	25	PRODUCTION	PRODUCTION	25
10	26	SETUP	STOP	40
10	26	BROKE	STOP	30
10	25	MAINTENANCE	MAINTENANCE	25
20	26	BROKE	STOP	20
20	25	PRODUCTION	PRODUCTION	15
20	26	PRODUCTION	PRODUCTION	40
20	25	SETUP	STOP	30
20	25	BROKE	STOP	25

I need to have a measure which calculate the percentage of any event. 

The percentage is calculated as "Event Minutes"/Availability. 

Availability is the sum minutes with type_event = PRODUCTION or STOP.

As first try, I just summarize all durations and I shown in a table as % of that measure. But if I need to filter some events, in this way every event has 100% if I filter an event. (that because Availability became the sum of durations of the event filtered.

So I tried using CALCULATE function combinated with ALL function. In this way, Availability became the total sum of my dirations all time long. But I need to see the duration filtered by week, machine, etc.

So, I would reach an Availability measure wich is the sum of all events with type_order PRODUCTION or STOP, not affected by filter on EVENT filed, but affected by all the other filters.

Then (and that would be the true goal) a measure that shows the percentage of all my events, based on the availability.

Some example:

-TOTAL availability of my table is 255 minutes( without filters)

-In this case, the % of Setup is (40+60)/255 = 23.5%.

If I want to know the % Setup on WK25:

-Availability: 125 minutes

-%Setup: 30/125 = 24%

If I want to know the %Broke on WK 26, machine 20:

-Availability: 60 minutes

-%Broke: 20/60 = 33%

Of course I have to show it also in table and graphs, grouping the measure in weeks, machines, and other filters. 

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Availability ratio: =
VAR _total =
    CALCULATE (
        SUM ( Data[Minutes] ),
        FILTER ( ALL ( Event ), Event[Event] <> "MAINTENANCE" )
    )
RETURN
    IF (
        HASONEVALUE ( Event[Event] ),
        IF (
            SELECTEDVALUE ( Event[Event] ) = "MAINTENANCE",
            "NA",
            DIVIDE ( SUM ( Data[Minutes] ), _total )
        )
    )

[andreazambon]

I tried to put your structure on mine. 

Tables with machine and week already exists, so I created the Events table taking informations from my DB.

I put in a measure the total of my durations in order to check if the composition of the availability hours is correct. And it is.

But the percentage is not available.

Disponibilità% =
VAR _total =
CALCULATE (
SUM ( SAPProdEvent[duration_in_day_h]),
FILTER ( ALL ( Eventi ), Eventi[Evento] <> "MANUTENZIONE" )
)
RETURN
IF (
HASONEVALUE ( Eventi[Evento]),
IF (
SELECTEDVALUE ( Eventi[Evento] ) = "MANUTENZIONE",
"NA",
DIVIDE ( SUM (SAPProdEvent[duration_in_day_h]), _total )

EDIT:

I tried to use a part of your code as the total:

Tot_ore_disp = 
CALCULATE (
        SUM ( SAPProdEvent[duration_in_day_h]),
        FILTER ( ALL ( Eventi ), Eventi[Evento] <> "MANUTENZIONE" )
    )

then I use my duration event to reach the percentage:

Disponibilità% = calculate(sum(SAPProdEvent[duration_in_day_h])/SAPProdEvent[Tot_ore_disp])

But it gains me 100% everytime, that is because it filter the selected event:

I supposed to not change the Availability (Tot_Ore_Disp) when I filter an event.