Conditional Formating Based on MAX and MIN of a Table

[Anonymous]

Hi community,

I need a Conditional Formating Based on MAX and MIN of a group of measures over a MATRIX table:

 https://www.dropbox.com/s/qg2q9t76gbwizzj/1.pbix?dl=0 

• (PBIX ATTACHED CALLED "1")
    a) I have 12 measures which I added into a matrix table (1 for every month of the year).
  
    b) I need to get the MAX number and MIN number outta these 12 measures which are in the table.
    In this example (MAX=11,85% and MIN= 3.20%) this changes according to the data.
  
    c) I need to get according to the data the MAX number and MIN number to use them as a
    condition
  
    d) with this MAX and MIN set the conditional formating for my 12 columns.
  
  All this must be dynamic. (anything manual)

Is this possible?

The PBIX is getting the result becasue the condition were put manually.

https://www.dropbox.com/s/qg2q9t76gbwizzj/1.pbix?dl=0 

According with what I wrote before, this would be the desired result:

Anyone could give me a hand?

[MFelix]

Hi @Anonymous ,

Is this the final result you need?

Be aware that this is not yet dinamic in selections just an overall formatting but is the same has you have, the colours are a little bit different but overall look is the one you need.

In the table above there is a single formatting and is not for each column/value.

If you answer previuos questions and also what is the dynamic part of the selection I can adjust the formulas to what you need.

[Anonymous]

Hi @MFelix it is... it looks the way I need it, but I get a little lost when you said it is not dinamic yet.

I would be amazed if you find a solution for this...it sounds easy but I think it is not because many people have said it is impossible (I mean many....in forums, informatic coleagues etc etc.)

[MFelix]

Hi @Anonymous ,

I just did the simple approach picking up the values you have and transforming them into the correct conttional formating.

What I need to know is what type of filters you are using when you refer to dynamic.

Are you filtering on date? on values? What are the columns you need to have filtering over.

That will take the change in the rest of the calculation, but believe is possible to achieve.

[Anonymous]

@MFelix when I say Dynamic Im talking about the color thing mainly ... I mean: the conditional formatting....

When I add data in the BBDD or filter by other dimensions that could make bigger or smaller the matrix table... but always need to considere max and min out of that filter... when Im talking dinamic is when I say that the min and max (my format condition) are changing according to the filters or data added.

Im doing in excel and it worked because I just mark the table area with conditional formatting... it is so easy... in PBI it has been a pain.

[MFelix · Accepted solution]

Hello @chromo4130 ,

I understood the needs and it was abble to obtain a solution that is dynamic, based on the selection of the cutter.

You should follow the steps below (note that this may need to be adapted to your model and visualization):

• Create a table with a list of the measures you want to calculate:

This is based on the columns you present in your image, don't forget to sort your measurement by the sort column,

• Change the % formulas to the following syntax:

M1%_V2 = VAR temp_table =
    SUMMARIZE (
        DetalleFacturado;
        DetalleFacturado[MesFacturacion];
        "@ImporteLinea"; SUM ( DetalleFacturado[ImporteLinea] );
        "@MValue"; SUM ( DetalleFacturado[M1] )
    )
RETURN
    SUM ( DetalleFacturado[M1] )
        / SUMX ( FILTER ( temp_table; [@MValue] <> BLANK () ); [@ImporteLinea] )

In the above code you need to create a formula for each M column and change that column M in both places was mentioned in the formula.

• Add the following additional measures:

Calculations =
SWITCH (
    SELECTEDVALUE ( 'Measure_Selection'[Measure] );
    "M1"; [M1%_V2];
    "M2"; [M2%_V2];
    "M3"; [M3%_V2];
    "M4"; [M4%_V2];
    "M5"; [M5%_V2];
    "M6"; [M6%_V2];
    "M7"; [M7%_V2];
    "M8"; [M8%_V2];
    "M9"; [M9%_V2];
    "M10"; [M10%_V2];
    "M11"; [M11%_V2];
    "M12"; [M12%_V2];
    "Faturado"; FORMAT ( SUM ( DetalleFacturado[ImporteLinea] ); "###,###" );
    "Q Cuentas"; FORMAT ( COUNT ( DetalleFacturado[MesFacturacion] ); "###,###" )
)

Formatting =
VAR Percentile_Calculation =
    CALCULATE ( [percentile]; ALLSELECTED ( DetalleFacturado ) )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Measure_Selection'[Measure] );
        "M1"; [M1%_V2] - Percentile_Calculation;
        "M2"; [M2%_V2] - Percentile_Calculation;
        "M3"; [M3%_V2] - Percentile_Calculation;
        "M4"; [M4%_V2] - Percentile_Calculation;
        "M5"; [M5%_V2] - Percentile_Calculation;
        "M6"; [M6%_V2] - Percentile_Calculation;
        "M7"; [M7%_V2] - Percentile_Calculation;
        "M8"; [M8%_V2] - Percentile_Calculation;
        "M9"; [M9%_V2] - Percentile_Calculation;
        "M10"; [M10%_V2] - Percentile_Calculation;
        "M11"; [M11%_V2] - Percentile_Calculation;
        "M12"; [M12%_V2] - Percentile_Calculation
    )

• Configure the table as follows:
  • Rows - Month-Anus
  • Columns - Measure (from the disconnected table created)
  • Values - Measurement of calculations
• Condittional format

Make sure that the central value is 0 and the default format is for white values.

Result then and in attach FILE PBIX has you can see changing the slicers the colors as well.

I think you should try to redo the M columns to be calculated in your PBIX file, but the result is the previous hope this helps, and thank you for tagging me on this because the is IMPOSIBLE always makes me take the extra step.

Check my blog post in this format (it's in Portuguese, but using google translate that are abble to read it).

https://pbiportugal.com/2020/05/06/formatacao-condicional-divergente/

We transfer binding for the PBIX file.

[MFelix]

Hi @Anonymous ,

There are a couple of question I have on your data and the file you have send out.

• The total line is different in your image and in your PBIX what 's is the correct one?
  • For example for M9 you have 3.2% as total and 0.35%
• This colouring is based no the percentile 50 of excel correct based no the maximum and minimum values?
• What are the rules for the M1 to M12 columns that you have?
  • Believe that you need to have this calculation made on measure to simplify everything else.

[Anonymous]

@Anonymous 

After some research and test, I afraid it is not possible to achieve your expected output making colors change according. 

You could create another measure to rule max and min, but for in-between values you would still need to set the range manually. 

Regards
Paul

[Anonymous]

WOWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW Never thought it was IMPOSSIBLE.

Not even get the data (M1%, M2%... in the table instead of in single measures) and then doing something like unpivot the table in power query?

What would @MFelix say aabout this crime?