cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Measure of a Measure - aggregating aggregated data?

I'm trying to figure out how to do aggregations of measures that are aggregating data by category.   Not even sure how well I'm describing this issue...

In my data, there are price changes coming in on a daily/sporadic basis.  I am taking the price as of the end of each week using a measure.  I need some other measures to refer to just the end of week prices, ignoring other price changes.  For example, averaging the price change over periods or across IDs.

Here's example data.  I'm able to get to end of week price with measures, but the averages are wrong and I have no idea how to get the average by store in this example.

My measure for the prices looks like:

``````Latest Price = CALCULATE(AVERAGE([Price]),
FILTER('Table',
'Table'[Price Change Date] =
CALCULATE(MAX('Table'[Price Change Date]),
FILTER('Table',
'Table'[Price Change Date] <= MAX(Weeks[Week Ended])
))
))``````

In my actual data there are more categories and things so it's more complicated, but I wanted a simplified example here.  In this example, the row subtotals are incorrect for what I'm going for, and I will need to do other aggregations by category beyond that.

1 ACCEPTED SOLUTION
Community Support

Hi, @ELW

If you need to fix the row totals for IDs, you can create a new measure like the following:

``````Measure 2 = IF(ISINSCOPE('Table'[ID]),

[Latest Price],DIVIDE(SUMX(ALL('Table'[ID]),[Latest Price]),DISTINCTCOUNT('Table'[ID]))
)``````

This will determine if the current row is a total row, and if so, perform the corresponding division. Here are the results:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi, @ELW

If you need to fix the row totals for IDs, you can create a new measure like the following:

``````Measure 2 = IF(ISINSCOPE('Table'[ID]),

[Latest Price],DIVIDE(SUMX(ALL('Table'[ID]),[Latest Price]),DISTINCTCOUNT('Table'[ID]))
)``````

This will determine if the current row is a total row, and if so, perform the corresponding division. Here are the results:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.