Forum Discussion

askelton's avatar
askelton
Resolver I
9 years ago
Solved

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?

 

 

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    askelton

     

     

    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, 

1 Reply

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    askelton

     

     

    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,