In this writing, I like to share my journey of tinkering with the RESET parameter in Power BI visual calculations.
I am really eager to solve one of the problems that I usually use WINDOW DAX function.
The problem I wanted to crack is how to calculate a sales running total while resetting the calculation every time sales dropped compared to the previous period.
It looks something like below (each country has different reset month).
Before I discovered visual calculations, I tried using the WINDOW DAX function. But I hit a roadblock when I saw an error message like this:
Nevertheless, fear not, for there's still a clever workaround at hand. Dive into the link provided below to uncover the solutions.
The moment I stumbled upon the RESET parameter in visual calculations, I was bursting with excitement. I couldn't wait to see if it held the key to solving the above challenge at hand.
However, a nagging thought crept in before I dived into visual calculations. I recalled that visual calculations are somewhat built upon the foundation of WINDOW DAX Family functions. But, filled with hope, I continued to explore, trying different methods... only to encounter the error message, as shown below.
I hold onto hope that eventually, PARTITIONBY DAX function will support columns added by DAX functions, or the RESET parameter in Visual Calculation will accept DAX formulas and other calculations.
But for now, here's the workaround I've experimented with to address this issue.
Generate a matrix visualization similar to the one shown below and incorporate the [Sales total:] measure. And the, click New calculation button on top.
Make three visual calculations that look like the examples below.
reset condition =
VAR _prev =
PREVIOUS ( [Sales total:], 1, ROWS )
VAR _current = [Sales total:]
RETURN
SWITCH ( TRUE (), _prev = BLANK (), 1, _current < _prev, 1, 0 )reset group =
RUNNINGSUM( [reset condition], ROWS)Cumulate Sales by condition reset =
VAR _currentgroup = [reset group]
VAR _currentmonthyear = [End of Month]
VAR _t =
ADDCOLUMNS (
ALL ( [End of Month] ),
"@sales", [Sales total:],
"@group", [reset group]
)
VAR _result =
SUMX (
FILTER ( _t, [End of Month] <= _currentmonthyear && [@group] = _currentgroup ),
[@sales]
)
RETURN
FORMAT ( _result, "#,#0" )
Or, add two more visual calculations like below. This makes not to use actual columns from the table, but to use visual_calculated_columns in WINDOW functions in visual calculation.
endofmonth = [End of Month]countrypartition = [Country]reset condition =
VAR _prev =
PREVIOUS ( [Sales total:], 1, ROWS )
VAR _current = [Sales total:]
RETURN
SWITCH ( TRUE (), _prev = BLANK (), 1, _current < _prev, 1, 0 )reset group =
RUNNINGSUM( [reset condition], ROWS)v2 =
SUMX (
WINDOW (
1,
ABS,
0,
REL,
ALL ( [endofmonth], [reset group], [countrypartition], [Sales total:] ),
ORDERBY ( [endofmonth], ASC ),
,
PARTITIONBY ( [countrypartition], [reset group] )
),
[Sales total:]
)
And, it looks like the below.
Or,
Comparing to the last blog post (link: How I create Cumulative Sums that resets whenever value is zero (jihwankimcscp.wixsite.com), making [reset condition] and [reset group] calculations in Visual Calculation is way easier. With these, making [Cumulative sales by condition reset] calculation becomes simpler too.
After creating these, just hide the [reset condition] and [reset group] calculations. Then, the final visualization looks like this:
Or,
In summary,
I explore how Visual Calculation makes it easier to create reset condition and reset group calculations compared to previous methods I've used in the previous blog post. Additionally, I emphasize the straightforward process of crafting a Cumulative sales by condition reset calculation. Furthermore, I express optimism for future enhancements, particularly hoping for improvements in the PARTITIONBY DAX function and RESET parameter within Visual Calculation to prevent encountering error messages, as mentioned earlier.
I trust that this will add more enjoyment to the exploration of the RESET parameter in Visual Calculation.
Comments