Forum Discussion

Powerbiuser77's avatar
Powerbiuser77
Frequent Visitor
6 years ago
Solved

Compare monthly value with a comparative value

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 com...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Powerbiuser77
Frequent Visitor
6 years ago

#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