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.
Hello Everyone!
I'm trying to create a measure that expresses prices as base 100, where the base 100 date is the earliest filtered date on a page level.
The dataset that I have contains volume and revenue data from december 2015 to december 2017. My approach so far was the following:
Price_Index = VAR Date_index = CALCULATE(MIN( 'SSD'[DATE] ),ALL(SSD)) RETURN (SUM(SSD[Val])/sum(SSD[Vol]))/(CALCULATE ( SUM ( SSD[Val] ), SSD[DATE] = Date_index )/CALCULATE ( SUM ( SSD[Vol] ), SSD[DATE] = Date_index ))*100
This gets the minimum date on the overall table, without any filters, and then calculates the current price (Value/Volume) divided by the price on the minimum date. This does provides an INDEX 100 for base December 2015, but what I want it to do is to calculate the base date from the minimum date on the plot, considering page level filters. That is, if I filter dates on a page level from December 2016 to December 2017, I want the base date to be december 2016. The problem is that when I remove the filters to get the Date_Index, it removes the page level filters as well.
I don't know if its possible the create something like a measure constant on DAX, that before any row level calculations get the minimum date on the table that is been used for the plot.
Do you have any ideas of how to work around this?
Thanks very much in advance!
Solved! Go to Solution.
HI @ncreixent
Try using ALLSELECTED instead of ALL
i.,e. to say
Price_Index = VAR Date_index = CALCULATE ( MIN ( 'SSD'[DATE] ), ALLSELECTED ( SSD ) ) RETURN ( SUM ( SSD[Val] ) / SUM ( SSD[Vol] ) ) / ( CALCULATE ( SUM ( SSD[Val] ), SSD[DATE] = Date_index ) / CALCULATE ( SUM ( SSD[Vol] ), SSD[DATE] = Date_index ) ) * 100
HI @ncreixent
Try using ALLSELECTED instead of ALL
i.,e. to say
Price_Index = VAR Date_index = CALCULATE ( MIN ( 'SSD'[DATE] ), ALLSELECTED ( SSD ) ) RETURN ( SUM ( SSD[Val] ) / SUM ( SSD[Vol] ) ) / ( CALCULATE ( SUM ( SSD[Val] ), SSD[DATE] = Date_index ) / CALCULATE ( SUM ( SSD[Vol] ), SSD[DATE] = Date_index ) ) * 100
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |