Percentage of maximum value

[Toa]

Hello,

I have a dataset looking like this :

[State]                  ....        [Sex]   ....                  [Value]

State_1                            M                             10

State_1                            F                               200

State_1                            U                               17

State_2                            M                               150

State_2                            F                                20

....

State_N                

State_N

I am using shape map to color every state according to it's value (with or without filters on Sex)

So if I don't use filter on Sex, I will have :

State_1 = 227

State_2 = 170

etc....

I am trying to represent the number of value by it's percentage of the maximum value (227 here, 150 if I filter on M, 200 if I filter on F)

The goal is to avoid extrem values on my shape map (so i can set maximum on the shape map. for example : if value > 75% of max value then color in the max color)

I am having troubles to calculate the maximum value with the filters (slider filters on sex and other columns), and then to apply this max value to calculate the percent.

I tried using measure :  

Measure = MAXX(GROUPBY('Table';'Table'[State];"Values";SUMX(CURRENTGROUP();'Table'[Value]));[Values])

which gives me the correct maximum value according to filters.

But when i try to calculate percentage with this measure (new column, each row <- DIVIDE(Value, Measure)), it doesn't work because the measure is recalculated for each rows (giving only 1 or NaN)

Any idea how to get ride of it ? Is it possible to use the Measure without recalculating it once it's first calculated ?

Thanks

[fhill]

Use the below pattern to add a Custom Column in Power BI (probably different formatting if you try to do it in Query Editor.)  The result is the % values of each Sex by State Grouping.

Value% = DIVIDE(Table2[Value],CALCULATE(SUM(Table2[Value]), FILTER(ALL(Table2), Table2[State] = EARLIER(Table2[State]))))

Now you can map the States with the Value% as the color Saturation.  Under Data Colors in the Formatting menu you can specify Min / Max values to be the extreem ends of your color patterns.  ** Don't forget your Max would be .75 for 75%, etc. **

Hope this helps!

FOrrest

[Toa]

Hi,

Thanks for your help, but this is not exactly what I am looking, the result here because the Value is divided by the max without filter.

In the example you used it would be :

Without filter :

Alabama : 227

Géorgia : 205

Max <- 227

Value% : Alabama : 1//Georgia : 0.9

Filter on M :

Alabama : 10

Georgia :  150

Max <- 150 

Value% : Alabama : 0.0667 // Georgia : 1

Filter on F :

Alabama : 200

Georgia : 20

Max <- 200

Value% : Alabama : 1, Georgia : 0.1

Filter on U :

Alabama : 17

Georgia : 35

Max <- 35

Value% : Alabama : 0.49, Georgia : 1

So the max value need to recalculate according to filters

EDIT :

I tried with this :

Value% = DIVIDE('Table'[Value];MAXX('Table';CALCULATE(SUM('Table'[Value]); FILTER(ALL('Table'); 'Table'[State] = EARLIER('Table'[State])))))

Which works almost fine exept the max value doesn't take the slider filter in consideration (max is always 227 no matter my filter)

EDIT2 :

I tried with :

Value% = DIVIDE('Table'[Value];MAXX(ALLEXCEPT('Table';'Table'[sex]);CALCULATE(SUM('Table'[Value]); FILTER(ALLEXCEPT('Table';'Table'[sex]); 'Table'[State] = EARLIER('Table'[State]))))) 

Which works when i use filter but not when i don't use filter... (the opposite of what happen in my 'EDIT' above)

[Toa]

Nobody has an idea ?

[Toa]

I am wondering why something like

Mesure = DISTINCTCOUNT('Table'[Sex])

Value% = 
DIVIDE(
          'Table'[Value];
          MAXX(
                  ALLEXCEPT('Table';'Table'[sex]);
                  CALCULATE(
                                SUM('Table'[Value]); 
                                FILTER(
                                    ALLEXCEPT(
                                         'Table';
                                         'Table'[Sex]
                                     ); 
                                    'Table'[State] = EARLIER('Table'[State])
                                  )
                     )
               )
) /[Mesure]

 Doesn't work.

In fact it calculates according to the Mesure value at the moment of first calcul, but when i use slicer to chose the Sex, the Mesure changes but the column doesn't recalcul... 

Is what i try to do feasible ?

[Toa · Accepted solution]

Finally found a way :

3 Measures :

Sum_Value =  SUM('Table'[Value])

Max_Value= MAXX(ALLEXCEPT('Table';'Table'[Sex]);CALCULATE(SUM('Table'[Value]); FILTER(ALLEXCEPT('Table';'Table'[Sex]); 'Table'[State] = EARLIER('Table'[State]))))

Max% = Divide(Sum_Value; Max_Value);

This works fine