Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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,
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |