Forum Discussion
Help with calculating Min/Max values for Chart
Hi,
I am very puzzled by this behaviour and want to seek community members advise. I am trying to determine Min/Max values of a chart using the DAX formula below:-
MaxOppWonValue_MthName =
MAXX (
ALLSELECTED ( 'Calendar'[Mth] ),
[OppWonValue]
)
MinOppWonValue_MthName =
MINX (
ALLSELECTED ( 'Calendar'[Mth] ),
[OppWonValue]
)
where [OppWonValue] is
OppWonValue =
CALCULATE (
SUM ( 'Opportunities'[Revenue] ),
'Opportunities'[Status] = "Won"
)
The issue is when I use the Mth column in Calendar table (eg Jan, Feb, Mar, Apr ...) as the common axis on the chart, the charts do not show the correct Min/Max values.
However, if I change the DAX formulas to MonthNumberOfYear and the chart axis to the MonthNumberOfYear, the calculations work.
MaxOppWonValue_MthNumber =
MAXX (
ALLSELECTED ( 'Calendar'[MonthNumberOfCalendarYear] ),
[OppWonValue]
)
MinOppWonValue_MthNumber =
MINX (
ALLSELECTED ( 'Calendar'[MonthNumberOfCalendarYear] ),
[OppWonValue]
)
What am I doing wrong or missing here?
An very hidden pitfall of "Sort by Column" setting. You may refer to this article,
Side effects of the Sort By Column setting in DAX - SQLBI
MinOppWonValue_MthName = MINX ( ALLSELECTED ( 'Calendar'[Mth] ), CALCULATE([OppWonValue], ALL('Calendar'[MonthNumberOfCalendarYear])) )
2 Replies
An very hidden pitfall of "Sort by Column" setting. You may refer to this article,
Side effects of the Sort By Column setting in DAX - SQLBI
MinOppWonValue_MthName = MINX ( ALLSELECTED ( 'Calendar'[Mth] ), CALCULATE([OppWonValue], ALL('Calendar'[MonthNumberOfCalendarYear])) )Wow, thanks for the quick response. I remember reading that particular article and making a mental note for future (which obviously didn't help in this instance hahaha).
End result that I was trying to achieve:
