cancel
Showing results for
Did you mean:
Resolver I

## Converting "Measure -> Calculated table -> Calculated Column", into single measure

I am fairly new to PowerBI and am working to complete the following goal.  I have a large data set in the form of columns A:D and I would like to have a measure which calculates column H, which I could filter and graph on my report.

While playing around with PowerBI I was able to create this roughly (in a way that doesn't work with filters on the report), in 3 main steps.

1) Weighted % Change Measure from raw data (In forms of columns A:D)

`WeightedMeasure = (Sum(CurrentExtended)-Sum(PastExtended))/Sum(PastExtended)`

2) Creating a intermediate calculated table with unique date values and the related weighted monthly % change

`Intermediate = distinct(Table1[Dates])`

and the calculated column :

`WeightedChange = Calculate([WeightedMeasure])`

3) I then used a calculated column to create the Index: (Luckily how my data is formulated, the Dec-16 is included in the unique values when making the intermediate table, and returns with a blank entry in the weighted measure field.

`Index = 100 * PRODUCTX ( filter (Intermediate, Intermediate[Dates] <= earlier(Intermediate[Dates])), Intermediate[WeightedChange] + 1)`

This compounds the growth from the base of 100 for each month and comes out with the correct answer.

However, this calculated column (and I believe the use of the intermediate table entirely), does not allow report filters to affect this.   So when I have 2 separate graphs on my report, one the WeightedMeasure over time, and one the Index over time.  The WeightedMeasure reacts to report filters, however the Index does not.

How can I get the index as a measure which reacts to the filters?

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Converting "Measure -> Calculated table -> Calculated Column", into single measu

Please specify the aggregation type in "Default Summarization", it supposes to respond to filter/slicer.

Or you can write your Index column into a measure like:

```index measure =
CALCULATE (
100
* PRODUCTX ( Intermediate, Intermediate[WeightedChange] + 1 ),
FILTER (
ALL ( Intermediate ),
Intermediate[Dates] <= MAX ( Intermediate[Dates] )
)
)```

Regards,

Microsoft

## Re: Converting "Measure -> Calculated table -> Calculated Column", into single measu

Please specify the aggregation type in "Default Summarization", it supposes to respond to filter/slicer.

Or you can write your Index column into a measure like:

```index measure =
CALCULATE (
100
* PRODUCTX ( Intermediate, Intermediate[WeightedChange] + 1 ),
FILTER (
ALL ( Intermediate ),
Intermediate[Dates] <= MAX ( Intermediate[Dates] )
)
)```

Regards,

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors