SUMMARIZECOLUMN and SUMX but as Calculated Measure in Calculated Table

[peterpbosse]

Update:

Link to pbix file: https://1drv.ms/u/s!AoMPf3EDnd3jmwNSu9lEcOAjbP_H?e=KQUCPh

Link to data file: https://1drv.ms/u/s!AoMPf3EDnd3jmwT8dkeHkwiIvr_0?e=j9ziBd

I have a data source table [Shift_Data] with downtime information (event length of time, category, shift information, timestamp, etc) used to generate a calculated table [OEE_Data] that sums the amount of time for each category of each shift (Ex: Idle Time), performance metrics are also calculated in the calculated table (Ex: % OEE).

I was given a request to implement an exclusion slicer as a “what if” scenario analyzer (If we eliminate X downtime category what would the performance metric look like).

I was able to implement the exclusion slicer with a calculated value in the source table that is either the reported length of time or 0 if the slicer category is selected.

However, the values in the calculated table do not update as they are generated at time of loading and dataset refresh.

Attempts to write queries similar to that in the calculated table formation as a new measure either do not calculate or yield a single value for all the shift-codes that does not update based on the exclusion slicer value.

Table:

“Shift_Data”

Measures:

Shift_Code; identifier/key for each shift, repeated for each reported downtime record on a given shift

Time(min); reported amount of downtime for a given event on a shift (multiple per shift)

BOEE Code; Categorization of Downtime Type (OEE Types)

Secondary; Categorization of Downtime Category (Process specific reasons)

isExcluded; Identifier if slicer category is selected, dynamically updates

isExcluded = if(MAX(Shift_Data[Secondary]) in ALLSELECTED(Secondary[Secondary]) && COUNTROWS(ALLSELECTED(Secondary))<>COUNTROWS(ALL(Secondary)),1,0)

Time_Exclude; calculated measure equal to Time (min) value or 0 if isExcluded

Time_Exclude = VAR FilterSecondary = FILTER(Shift_Data,[isExcluded]=0) return CALCULATE(SUM(Shift_Data[Time (min)]),FilterSecondary)

Table:

“OEE_Data”

OEE_Data =

SUMMARIZECOLUMNS(Shift_Data[Shift-Code],
"Avg_Line_Speed_fpm", AVERAGE(Shift_Data[Act Line Speed (fpm)]), "Idle_Time_mins",SUMX(FILTER(Shift_Data,Shift_Data[BOEE Code]="IT"),[Time_Exclude])
)

{Note: This is not the entire length of the table generation query, just an excerpt of general usage of SUMX and Average to generate table}

Measures:

Below is one of my attempts to calculate Idle_Time_mins_excluded inside of OEE_Data table as a new measure based on selected exclude slicer that is not working.

Idle_Time_mins_Exclude =

CALCULATE (

    SUM ( Shift_Data[Time (min)] ),

    FILTER (

        All( Shift_Data ),

        Shift_Data[BOEE Code]="IT"

    )

)

Exclude slicer functions as expected on Time_Exclude value in Shift_Data table

OEE_Data table values sum by Shift_Data[Shift_Code] at time of table creation, but is not for excluded Shift_Data[Time_Excluded] values.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[peterpbosse]

I believe I updated the original post just now, but also here are links to the requested exampl pbix and data files.

Link to pbix file: https://1drv.ms/u/s!AoMPf3EDnd3jmwNSu9lEcOAjbP_H?e=KQUCPh

Link to data file: https://1drv.ms/u/s!AoMPf3EDnd3jmwT8dkeHkwiIvr_0?e=j9ziBd