Forum Discussion
Anon2020
Helper I
1 year agoDynamic Column Difference in Matrix
I have a matrix set up with Total sales by FY by Producyt Code. I am unable to find a formula that accurately depicts the difference betyween the two selected years that the user chooses from a filte...
- 1 year ago
Hi Anon2020
It seems to me that you're trying to show the min and max selected years and a column for the difference, if so try this DAX measure
Min Max Difference = VAR _minYr = MINX ( ALLSELECTED ( CalendarTable ), CalendarTable[Year] ) VAR _maxYr = MAXX ( ALLSELECTED ( CalendarTable ), CalendarTable[Year] ) VAR _years = { _minYr, _maxYr } VAR _minYrValue = SUMX ( FILTER ( ALLSELECTED ( CalendarTable[Year] ), CalendarTable[Year] = _minYr ), [Total Sales] ) VAR _maxYrValue = SUMX ( FILTER ( ALLSELECTED ( CalendarTable[Year] ), CalendarTable[Year] = _maxYr ), [Total Sales] ) RETURN SWITCH ( TRUE (), NOT ( HASONEVALUE ( CalendarTable[Year] ) ), _maxYrValue - _minYrValue, SELECTEDVALUE ( CalendarTable[Year] ) IN _years && HASONEVALUE ( CalendarTable[Year] ), [Total Sales] )Please see the attached pbix.
danextian
Super User
1 year agoHi Anon2020
It seems to me that you're trying to show the min and max selected years and a column for the difference, if so try this DAX measure
Min Max Difference =
VAR _minYr =
MINX ( ALLSELECTED ( CalendarTable ), CalendarTable[Year] )
VAR _maxYr =
MAXX ( ALLSELECTED ( CalendarTable ), CalendarTable[Year] )
VAR _years = { _minYr, _maxYr }
VAR _minYrValue =
SUMX (
FILTER ( ALLSELECTED ( CalendarTable[Year] ), CalendarTable[Year] = _minYr ),
[Total Sales]
)
VAR _maxYrValue =
SUMX (
FILTER ( ALLSELECTED ( CalendarTable[Year] ), CalendarTable[Year] = _maxYr ),
[Total Sales]
)
RETURN
SWITCH (
TRUE (),
NOT ( HASONEVALUE ( CalendarTable[Year] ) ), _maxYrValue - _minYrValue,
SELECTEDVALUE ( CalendarTable[Year] )
IN _years && HASONEVALUE ( CalendarTable[Year] ), [Total Sales]
)
Please see the attached pbix.