Forum Discussion

thomasreick's avatar
thomasreick
Icon for Resolver I rankResolver I
2 years ago
Solved

Colouring measure not working properly

This is related to my last question

 

I want use a measure comparing against monthly figures.

The number of months is coming from a slicer.

The used measures are....

  • [# Contracts] = COUNTROWS(tblFacts)
  • [ØContractsMonthly] =
    var tbl = ADDCOLUMNS( VALUES( calendar[YYYYMM] ), "Contracts", [# Contracts]
    return AVERAGEX( tbl, [# Contracts])
  • [ColumnColorContracs]=
     var check = [# Contracts]
    var limit = [ØContractsMonthly]
    return SWITCH( TRUE(), check >= limit, "Green", "Red" )

[# Contracts] is used as Value for Y-Axis, calendar[YYYYMM] is on X-Axis, Reference Line is using [ØContractsMonthly]

 

I have figured out the following:

Using a constant value (e.g. 1500) instead of [ØContractsMonthly] makes colouring work correctly.

  1. How can this be?
  2. How to make it dynamically?

 

Any help or hint or support is appreciated.

 

thx in advance

 

Thomas

DAX help needed
Hints & Tips
  • thomasreick's avatar
    thomasreick
    2 years ago

    I have found the solution with a litle help frmo my friend ChatGPT....

    TotalAverage=

    VAR TotalSum = CALCULATE(SUM(facttable[countablevalue]), ALLSELECTED(calendar))
    VAR TotalPeriods = CALCULATE(DISTINCTCOUNT(calendar[YYYYMM]), ALLSELECTED(calendar))

    RETURN

        IF(

            TotalPeriods > 0,

            TotalSum / TotalPeriods ,

            BLANK()

        )

     

    I did not paid attention to filtercontext.

     

    Thank you very much for readng and input.

     

    Thomas

2 Replies