Forum Discussion
Compare monthly value with a comparative value
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
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
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?
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
#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