Forum Discussion

OmarSek's avatar
OmarSek
Regular Visitor
4 years ago
Solved

Conditional formatting using a value who change every year

I am working in project to calculate a score of each company of a same group.    To calculate the score , each company has to provide x kpi each month ( x is a number of kpi that the company have t...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    According to your description, I create a sample.

    Here's my solution.

    1. Create a KPI table, don't make relationship between the two tables.

    2. Create a measure.

    Color =
IF (
    MAX ( 'Table'[Score] )
        >= MAXX (
            FILTER (
                ALL ( 'KPI BY YEAR' ),
                'KPI BY YEAR'[Year] = YEAR ( SELECTEDVALUE ( 'Table'[Date] ) )
            ),
            'KPI BY YEAR'[KPI]
        ),
    "Blue",
    "Grey"
)

    3. Select the stacked colum chart, in the Format pane, select Columns>Colors>fx, select the color measure in the dialog.

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

     

OmarSek's avatar
OmarSek
Regular Visitor
4 years ago

I like the idea, i will try to implement it and i will let you know the result.

OmarSek's avatar
OmarSek
Regular Visitor
4 years ago

I create a table called KPI BY YEAR , with two columns YEAR and Num KPI , this table has a relationship with just Date table . 

I have a slicer of month, i am trying just to find the NUM KPI using dax formula but it doesn't function. 

This is my dax forumula : 

NumPKIYEAR = VALUES ('KPI BY YEAR'[Num KPI]) 

but i get an error.