cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
askelton Resolver I
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.  

Index.PNG

 

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
Microsoft

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

@askelton

 

5.PNG

 

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, 

View solution in original post

1 REPLY 1
Microsoft
Microsoft

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

@askelton

 

5.PNG

 

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, 

View solution in original post

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors