Conditional formatting matrix by ROW

[drivero]

Hello,

I would like to have a conditional scale colours from low to high but done by rows.

Like now, I have it by all the Metric total I used, but I really want it by row. Could you help me?

My matrix is this one:

Its configuration is:

Where KPI is the table with the Name of the row as:

Where Mes is the month (columns)

And where Actual is the Metric as follows:

I would like to determine for LITROS, the worst and the best month by number, and the same for all the other KPI rows.

Please, need help, thank you.

[drivero]

@Anonymous thank you

[Anonymous]

Hi @drivero ,

I'm sorry I didn't quite understand what you mean, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

[drivero]

Hello,

I use Analysis Service, I cannot simplifie the pbix file.

Let's work with this scenario, simplified.

Table: KPI_Principales
Columns: KPI, Order
Values: {Litros, 1}, {MB, 2}, {Gastos, 3}

Table: DWH DimDate (for months)

Measure: Total Value = 
VAR RowResume = SELECTEDVALUE ('DWH KPI_Principales'[KPI])

RETURN
SWITCH (
RowResume,
"Litros", [Total Litros],
"MB", [Margen Bruto],
"Gastos", [Total Gastos])

Where [Total Litros] = SUM('DWH Operation'[Amount])

Where [Margen Bruto] = SUM('DWH Finance'[MB])

Where [Total Gastos] = SUM('DWH Actual'[Amount])

In this case, three diferents Items from table KPI, are the measures from three different tables as you can see above.

Hope this clarify better your doubts. Thank you!

[Anonymous]

Hi @drivero ,

Please try this :

Rank by Month = 
       RANKX (
        FILTER(ALLSELECTED( 'KPI_Principales'),[KPI]=MAX([KPI])),
        [Total Value] ,
        ,
        ASC
    )

Best Regards,

Neeko Tang

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

[drivero]

Hi @Anonymous 

The measure is Fine, but when I do the conditional formattins, I just only see one color.

[drivero]

Hello,

thank you! But the problem is your DATA table I do not have it, my mesure related to your Actual Measure is a Switch depending on the field on the table KPI and has different Measures from other tables.

How I can do this:

Rank by Month = 
       RANKX (
        FILTER(ALLSELECTED( 'Data'),[KPI]=MAX([KPI])),
        CALCULATE(SUM(Data[Actual])) ,,
        ASC
    )

[Anonymous]

Hi @drivero ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.  

Data:

KPI:

Month:

(2) We can create two measures. 

Actual measure = SUM('Data'[Actual])

Rank by Month = 
       RANKX (
        FILTER(ALLSELECTED( 'Data'),[KPI]=MAX([KPI])),
        CALCULATE(SUM(Data[Actual])) ,,
        ASC
    )

(3) Set conditional formatting and then the result is as follows.

Best Regards,

Neeko Tang

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