Measure for colouring columns on a higher aggregation level than detailed data
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 Thomashow to... Create Measure to identify "direction" of development ofranking from two periods
Hello there, I would like to create a measure to indicate the development of rankings per item from two periods (RankActual=a, RankPrevious=p). Because both values might be <0 or > 0 or = 0, I need a hint how to handle that in a smart way. My approach seems not good. I did some IF-THEN-ELSE-syntax but the resulting indicator -1 | 0 | +1 is not set correctly IF ( AND( a<0, p<0 ), a | < | = | > p ) is varying the relations only, but anyway there is a positive development indicated, although not positive logically... Example: a=-6, p=-4 leads to a < p the delta is (a-p) = -2, the movement is 2 points DOWN., but it is shown as upward-movement. Actually I am stuck on this and kindly request your input. thank you very much Thomas