The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi there,
i am trying to make a new measure in my data, this measure is supposed to be the same as AVERAGEIFS in excel, it looks like
sc by group =
CALCULATE(
AVERAGE('AGGREGATE_GLOBAL'[sc]),
FILTER (
'AGGREGATE_GLOBAL',
[year] = EARLIER ( 'AGGREGATE_GLOBAL'[year] )
&& [month] = EARLIER('AGGREGATE_GLOBAL'[month])
&& [group_store] = EARLIER('AGGREGATE_GLOBAL'[group_store])
)
)
and should count an average value of column [sc] by [year], [month] and [group_store].
Unfortunately, as far as I know I cannot use the function EARLIER in measure(it returns
ARLIER/EARLIEST refers to an earlier row context which doesn't exist
), so then I've tried to make a new calculated column with the same code. But in that case it returns
Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models.
Because I use DirectQuery mode and cannot use Import.
How can I solve it?
Solved! Go to Solution.
Hi @pocorniy,
According to your description, it sounds like you want to apply multiple aggregation on your records calculations.
For this scenario, you can take a look the following link about multiple aggregation calculation in measure expressions if helps:
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
Hi @pocorniy,
According to your description, it sounds like you want to apply multiple aggregation on your records calculations.
For this scenario, you can take a look the following link about multiple aggregation calculation in measure expressions if helps:
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
@pocorniy I think you need to clarify the granularity, do you want to the average for daily, monthly, quarterly or yearly? then you can use variable to create a virtual table to calculate the average, hopefully this will help.
@Walter_W2022 in short, to clarify what I need, I have a table with this columns (not all, but enough):
year; month; store; group_store; sc
2022; 8; 23; A; 12
2023; 9; 15; B; 18
etc.
So, i need a measure that will calculate an average value of the column sc for each year, month and group_store. I need it in order to see 2 lines in a graph: first one is value for one store (or average for couple of them, depends on filters) and second one that will reflect an average value for this particular group of stores (i have three -- A, B, C). When I choose one store in filter, i want to see an average value for all stores in his group.
How can I do this?
thanks for help
I have montly granularity of data and want to calculate the average value also monthly;
I need this value to be in my general table for the correct vizualization. Is it possible?
User | Count |
---|---|
17 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
12 | |
9 | |
8 |