Get highest value in Graph to use as line

[Wresen]

Hi and thanks for reading this.

I have a line and stacked column chart and i want to use the highest colum value to use in the line value

I want it to be in a measure  and the value for the line should change to the highest value for the Item that has been marked (Lamp A or Lamp B)

Below is the rawdata table that is used here.

/Thanks

[v-lionel-msft]

Hi @Wresen ,

Try this:

Max1 = 
CALCULATE(
    SUM('Table'[Value]),
    ALLEXCEPT(
        'Table',
        'Table'[Item]
    ),
    ALL('Table'[Month])
)

Best regards,
Lionel Chen

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

[Wresen]

Hi @v-lionel-msft

thanks so much ,but is it possible to get the highest value for the months , now it takes the total sum for all months.

So for Lamp A its will be 23 , and Lamp B  14

/Thanks

[Wresen · Accepted solution]

HI 

I found a  solution :

Measure 1: 

Max2 = SUM('Table'[Value])

Measure 2:

Maxx = maxx(VALUES('Table'[Month]);[Max2])

Measure 3  (modifid code that i got from @v-lionel-msft )

Max1 = CALCULATE( [Maxx]; ALLEXCEPT( 'Table'; 'Table'[Item] ); ALL('Table'[Month]))

[v-lionel-msft]

Hi @Wresen ,

Yes, you can.

1. Add a custom column

2.Split [month] column into [Year] column and [Month] column

3. Create two measure separately

Measure_sum = 
CALCULATE(
    SUM('Table'[Value]),
    ALLEXCEPT(
        'Table',
        'Table'[Item], 'Table'[Month]
    )
)

Measure_max = 
CALCULATE(
    MAXX(
    'Table',
    [Measure_sum]
    ),
    ALL('Table'[Custom])
)

Best regards,
Lionel Chen

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

[amitchandak]

Max =
var _1 = calculate(sum(value),table[item]="Lamp A")
var _2 = calculate(sum(value),table[item]="Lamp B")
return
max(_1,_2)

[Wresen]

Hi @amitchandak 

Thanks so much for the suggestion, but i dont think i works, the max value for Lamp A (in this case) should be a straigh line and the max value is in Feb and is 23 , so the line should be 23 for all months.

And i will have over 1000 items in the total file.

[amitchandak]

Try with

Max =
var _1 = sumx(filter(table,table[item]="Lamp A"),Table[value])
var _2 = sumx(filter(table,table[item]="Lamp B"),Table[value])
return
max(_1,_2)

[Wresen]

Hi @amitchandak 

I am sorry but that measure is not working , its gives me a dax error

[amitchandak]

@Wresen , correct the table name at your end. It was Sheet1, last time

[Wresen]

Hi @amitchandak 

I renamed Sheet1 to Table.

I am sorry but i do not get that code to work

[amitchandak]

@Wresen ,

Can you share the sample data in table format

[Wresen]

Hi @amitchandak 

i have upploaded the whole file below.

If its possible do an auto "item" i dont need to code every item since i will have over 1000 and i never know what items there will be from month to month

https://drive.google.com/open?id=1s0hrA5aGZad75sTEbX5yxPcuaKAQgqP1