Skip to main content
cancel
Showing results for 
Search instead 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

Reply
ELW
Advocate II
Advocate II

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.  

Measure of a Measure Question.PNG

 

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. 

 

PBIX is here: https://drive.google.com/file/d/1KGz_dHHmMIDM9uyCh7l7O3gI6W3TYkj8/view?usp=sharing 

 

Thanks in advance!

1 ACCEPTED SOLUTION
v-jianpeng-msft
Community Support
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:

vjianpengmsft_0-1718330243740.png

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.

View solution in original post

1 REPLY 1
v-jianpeng-msft
Community Support
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:

vjianpengmsft_0-1718330243740.png

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors