Compare monthly value with a comparative value

[Powerbiuser77]

Hi Experts,

I would like to realize the following project and need help. In general, I want to track error rates. I tried the following steps:

Step 1: For each error code should be calculated a comparative value. To this end, I calculate for each month, 12 months laid back from the current month, an average value. Then I calculate a total average value from the monthly average values that serves as a comparative value. In this example, the calculated comparative value is 6 (s. picture below Step 1 first Table).

Step 2: The next step is to compare the amount of errors from the current month with the comparative value. If this is exceeded, should be output "Yes" or "No" in the Exceeded column. In this example, PrID M5 would have 8 > 6 = true -> yes (s. picture below Step 2 second Table). If this happens three months in a row, should be displayed "conspicuous" in the Notification column.

Step 3: Output of the notification in the Tracking/Monitoring list (s. picture below Step 3 third Table).

I need help for the following steps in Power Bi:

Step 1: How do I calculate the comparative value (average of average) 12 months laid back from the current month?

Step 2: Could not implement the compare operation as described in step 2 above.

For My Power Bi .pbix File see Link and for the Dataset.  Any help is greatly appreciated. Thanks a lot and please excuse my poor english.

@Ashish_Mathur 

[v-shex-msft · Accepted solution]

HI @Powerbiuser77,

You can try to use below calculate table formula to export summarised result table:

Table = 
VAR pyearSummary =
    FILTER (
        GROUPBY (
            ADDCOLUMNS (
                alarm_history,
                "Year", YEAR ( [TimeOn Day] ),
                "Month", MONTH ( [TimeOn Day] )
            ),
            [Alarmcode],
            [PrID],
            [Year],
            [Month],
            "Amount", COUNTX ( CURRENTGROUP (), [Alarmcode] )
        ),
        [Year]
            = YEAR ( TODAY () ) - 1
    )
VAR mutli_aggreated =
    GROUPBY (
        GROUPBY (
            pyearSummary,
            [Alarmcode],
            [Year],
            [Month],
            "AVG", AVERAGEX ( CURRENTGROUP (), [Amount] )
        ),
        [Alarmcode],
        "TotalAVG", AVERAGEX ( CURRENTGROUP (), [AVG] )
    )
VAR cyearSummary =
    ADDCOLUMNS (
        GROUPBY (
            FILTER (
                ADDCOLUMNS (
                    alarm_history,
                    "Year", YEAR ( [TimeOn Day] ),
                    "Month", MONTH ( [TimeOn Day] )
                ),
                [Year] = YEAR ( TODAY () )
                    && AND ( [Month] >= MONTH ( TODAY () ), [Month] <= MONTH ( TODAY () ) + 3 )
            ),
            [Alarmcode],
            [PrID],
            [Year],
            [Month],
            "Amount", COUNTX ( CURRENTGROUP (), [Alarmcode] )
        ),
        "Exceed", IF (
            [Amount]
                > MAXX ( FILTER ( mutli_aggreated, [Alarmcode] = EARLIER ( [Alarmcode] ) ), [TotalAVG] ),
            "Yes",
            "No"
        )
    )
RETURN
    SUMMARIZE (
        cyearSummary,
        [Alarmcode],
        [PrID],
        [Exceed],
        "FirstExceed", MINX (
            FILTER (
                cyearSummary,
                [Alarmcode] = EARLIER ( [Alarmcode] )
                    && [PrID] = EARLIER ( [PrID] )
                    && [Exceed] = "Yes"
            ),
            [Year] * 100 + [Month]
        ),
        "Notice", COUNTROWS (
            FILTER (
                cyearSummary,
                [Alarmcode] = EARLIER ( [Alarmcode] )
                    && [PrID] = EARLIER ( [PrID] )
                    && [Exceed] = "Yes"
            )
        ) > 3
    )

Regards,

Xiaoxin Sheng

[Powerbiuser77]

Hi v-shex-msft,

thank you for the Extensive code. I have inserted the code into my Power Bi pbix file. I don't see any results right now. If I understand the code correctly, the exceed comparison starts with today's month and year. Since I only have data until March 2020, I don't see any results. Is my assumption correct?

I would like to create a second summarised result table using the same code. In this new table, the results from the selected year should be displayed (via the drop-down menu). For example, if I selected 2019 in the drop-down menu, the PrID M5 with the alarm code 850 whose monthly value in October, November and December has exceeded the comparative value should be displayed (see picture in Link__).

Tried to change Year (TODAY) but I was unsuccessful (see new file Link_). My code knowledge is not sufficient. How can I modify the code so that I can also check the past years for the frequency of errors in a new summarised result table?

[v-shex-msft]

Hi @Powerbiuser77,

#1, Yes, this summary table is based on inputted date value. You can modify it to change the summarized date ranges.

#2, Did you mean to use a filter to control the summary table results data ranges? If this is a case, current it is impossible. Summaries table results are based on inputted date value, it not interaction with filter/slicers which you chose. (they are stored in different data levels, filter/slicer are based on the virtual table who generated from data model tables, you can't use child level to effect its parent)
Regards,

Xiaoxin Sheng

[Powerbiuser77]

#2, Correct, I wanted to use the drop-down menu to display the exceedances for the selected year as shown in the image below or something like the following examples 1 (selection by weeks) and 2 (selection by years). I inserted your code in a new summarised rsult table and tried to replace "Today" with "Lastdate" and "Selectedvalue", but it didn't work (see pbix-File Table 2). If I partially understood you, filtering by years in a summarised result table is not possible. Is it possible to solve the problem with individual Dax formulas instead of a summarised result table? I tried something similar in my bpix-File Tabele 4. Only the notice part is missing in table 4. I tried to extract the code for Notice from your code, but unfortunately I couldn't get it to work.

Enclosed the new version of my pbix-File.

Best regards