Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello people!
I'm still new to Power BI, so forgive me if I am making some kind of primary mistakes.
I have this measure, that gives me a rolling average for each month of the year:
TESTE = CALCULATE(AVERAGEX(VALUES(DateKey[Data]);'Base Ligações'[Total Ligações]);DATESINPERIOD(DateKey[Data];MAX(DateKey[Data]);-3;MONTH))
Without any filters, the measure shows up the average of the present month (which is composed by the sum of 'Base Ligações'[Total Ligações] from the current month and the last two months, then divided by 3). If a month filter is applied via a slicer, it shows correctly the average of that montht and it's two predecessors.
What I want now is to sum all of those averages,i.e., I want the total of all averages for a specifc year, keeping the correct results if a specific month is selected via a filter in a slicer.
I've tried SUM, SUMX on the TEST measure, but it simple doesn't work at all.
I appreciate any help!
Solved! Go to Solution.
Hi @Anonymous,
have I understood you correct: when no month is selected, you want to sum the averages like this: average(dec, nov, oct) + average(nov,oct,sep) + average(oct,sep,aug) + ......
You state you want to see it for specific year, where should the series above end, at average(mar, feb, jan) or (jan,dec,nov)?
If I have understood you, and you want the series to end at (mar,feb,jan)+(feb,jan)+(jan) this will work(probably, I have just tested in on a very simple mockup model i created, without the option for timeintelligence functions, so the version of [teste] I created were not exactly like yours)
Measure =
IF (
NOT ( HASONEVALUE ( test[month] ) );
SUMX ( VALUES ( test[month] ); [TESTE] );
[TESTE]
)
Now, if you want to have you series to end at (jan,dec,nov), it is a bit more complicated. I had to rewrite your measure to this:
TESTE =
VAR _maxMonth =
CALCULATE ( MAX ( test[month] ) )
VAR _prevMonth =
DATE ( YEAR ( _maxMonth ); MONTH ( _maxMonth ) - 2; 1 )
RETURN
DIVIDE (
CALCULATE (
SUM ( test[salg] );
FILTER ( ALL ( test ); test[month] <= _maxMonth && test[month] >= _prevMonth )
);
COUNTROWS (
CALCULATETABLE (
VALUES ( test[month] );
FILTER ( ALL ( test ); test[month] <= _maxMonth && test[month] >= _prevMonth )
)
)
)
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
Hi @Anonymous,
have I understood you correct: when no month is selected, you want to sum the averages like this: average(dec, nov, oct) + average(nov,oct,sep) + average(oct,sep,aug) + ......
You state you want to see it for specific year, where should the series above end, at average(mar, feb, jan) or (jan,dec,nov)?
If I have understood you, and you want the series to end at (mar,feb,jan)+(feb,jan)+(jan) this will work(probably, I have just tested in on a very simple mockup model i created, without the option for timeintelligence functions, so the version of [teste] I created were not exactly like yours)
Measure =
IF (
NOT ( HASONEVALUE ( test[month] ) );
SUMX ( VALUES ( test[month] ); [TESTE] );
[TESTE]
)
Now, if you want to have you series to end at (jan,dec,nov), it is a bit more complicated. I had to rewrite your measure to this:
TESTE =
VAR _maxMonth =
CALCULATE ( MAX ( test[month] ) )
VAR _prevMonth =
DATE ( YEAR ( _maxMonth ); MONTH ( _maxMonth ) - 2; 1 )
RETURN
DIVIDE (
CALCULATE (
SUM ( test[salg] );
FILTER ( ALL ( test ); test[month] <= _maxMonth && test[month] >= _prevMonth )
);
COUNTROWS (
CALCULATETABLE (
VALUES ( test[month] );
FILTER ( ALL ( test ); test[month] <= _maxMonth && test[month] >= _prevMonth )
)
)
)
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
@sturlaws , your first suggestion worked like a charm!
But I really doesn't understand how and why..can you explain it?
Thanks!
Sure thing.
When you choose e.g. year = 2019 in a slicer, the [month]-column contains all the months of 2019. So the HASONEVALUE-function returns FALSE. I have wrapped NOT() around it, so the condition in the IF-function evaluates to TRUE if the HASONEVALUE-function evaluates to false.
When the condition evaluates to TRUE, the SUMX-function is called. SUMX is an iterator-function. The first argument of SUMX is a table(or table expression). In this case, VALUES(test[Month]) returns a 1-column table with the distinct [months] in the current context. So if year = 2019 in a slicer, VALUES(test[Month]) returns the months of 2019. The second argument of SUMX() is the measure [TESTE]. This measure is evaluated once for every row in the table generated by VALUES([month]), with the corresponding month as context for the measure evaluation. When it is evaluated for all rows in VALUES([month]) the results are summed up and returned.
There is one last thing to consider with this measure. If you use a slicer(or any other filter) to choose year=2019, that is part of the context for which the measure is evaluated. So for january only the january value is added to the average, because the dec-2018 and nov-2018 is not present in the context of 2019.
Now, if the is something filtering the [month]-column, e.g. a slicer or crossfiltering from another table, HASONEVALUE returns true. Then [TESTE] is just evaluated for just that month.
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |