topic Re: Colouring measure not working properly in DAX Commands and Tips

Colouring measure not working properly

thomasreick — Fri, 12 Apr 2024 06:28:30 GMT

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.

How can this be?
How to make it dynamically?

Any help or hint or support is appreciated.

thx in advance

Thomas

Re: Colouring measure not working properly

lbendlin — Sat, 13 Apr 2024 00:41:02 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Colouring measure not working properly

thomasreick — Sun, 14 Apr 2024 09:53:53 GMT

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