topic Re: Measure for colouring columns on a higher aggregation level than detailed data in DAX Commands and Tips

Measure for colouring columns on a higher aggregation level than detailed data

thomasreick — Wed, 10 Apr 2024 10:16:54 GMT

Hi there I kindly request your help/hints on the follwing, because I am stuck somwhere in between...
I need to color a column chart based on the average out of X Months, whereas No of months is from a relative date slicer

The data are weekly based (Date of Sunday) and is counting number of contracts:

MyMeasure = Sum( MyTable[NoOfContracts] )

Using my datetable I am visualising MyMeasure in a columnchart
I wrote a measure to get the average per No of selected Months:

MyMonthlyAverage = AVERAGEX ( VALUES( calendar[MonthYear]), MyMeasure )

Based on the relation between MyMeasure and MyMonthlyAverage I want to decide the color of the column, "Geen" for above, "Red" for below MyMonthlyAverage.
Unfortunateley it seems that the number per Month (MyMeasure) is not divided by no of months but by the number of Sunday-dates in the selected period (e.g. MyMonthlyNoOfContracts / No of Sunday-Dates (52) and not MyMonthlyNoOfContracts / No of Months selected (12))

MyColumnColor =

var check = [MyMeasure]
var limit = [MyMonthlyAverage]
return SWITCH( TRUE(), check < limit, "Red", "Green")

Aways "Green" gets returned and so are the columns

Question: What is the Syntax to write a correct and proper MyMonthlyNumberOfContracts?

Appreciating your Input

thx in advance

Thomas

Re: Measure for colouring columns on a higher aggregation level than detailed data

johnbasha33 — Wed, 10 Apr 2024 10:56:51 GMT

thomasreick

To calculate the monthly average of the number of contracts correctly, you need to ensure that the calculation is based on the number of months selected in your slicer, rather than the number of Sunday-dates in the selected period. You can achieve this by modifying your measure to consider the number of months selected.

Here's how you can modify your measure to calculate the correct monthly average of the number of contracts:

MyMonthlyNumberOfContracts =
VAR SelectedMonths = CALCULATE( COUNTROWS( VALUES( Calendar[MonthYear] ) ) )
RETURN
DIVIDE( [MyMeasure], SelectedMonths, 0 )

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Re: Measure for colouring columns on a higher aggregation level than detailed data

thomasreick — Wed, 10 Apr 2024 16:00:59 GMT

Hi john basha33,
thank you very much fpr your input, but the returned value matches the value from this measure below

ØContractsMonthly =
var tbl = ADDCOLUMNS( VALUES( calendar[YYYYMM] ), "Contracts", [# Contracts]
return AVERAGEX( tbl, [# Contracts])
Your solutino (as mine) returns the Average valid for all periods selected

That seems fine.

So my question is not answered yet..., why is

MyColumnColor =

var check = [MyMeasure]
var limit = [MyMonthlyAverage] (<= [MyMonthlyNumberOfContracts] <= [ØContractsMonthly])
return SWITCH( TRUE(), check < limit, "Red", "Green")

... alwyays returns green color as result for all columns although the bar height is obviously below or above reference line composed of limit-value....

Any suggestoins?

Your help is appreciated.

thx & brgds

Thomas