Line which shows the values of the bars, but + 20% of the highest bar chart value

[scoobydoo]

Hello community,
I want to show my "total sales" in bars by month/year and have them labeled not only with "total sales", but also with the "growth". There seems to be no option for this, except of building a measure that combines both in one label, but without a line break it's too long. 

My workaround: Adding a line to the bar chart and label it with my growth value. But this line should not have exactly the same value as the bar, otherwise there would be an overlay. Therefore, I want the line to have a distance from the bar, but this distance should be constant and not vary from month to month. My idea was to take 20% of the "maximum total sales" value within my visual and add it to the "total sales". This is the measure for my new line:

Sales MAX = 
    VAR _MaxSales = MAXX(ALL('Datetable'[MonthYear]), [Total Sales])
    VAR _Offset = _MaxSales * 0.2
    RETURN
        [Total Sales] + _Offset

My problem: Whatever I try, the new line doesn't have a constant distance. It gets always affected by the Month/Year of the x-Axis and calculates the 20% on that particular month instead of the Maximum Month.

Do you know how to solve this problem? That would be great!
Many thanks in advance 🤗

[scoobydoo]

Thank you for the explanation, I will adjust the measure accordingly. 
Regarding the SUMMARIZE-function: I have the Month/Year in a separate datetable, that's why i used it in the statement. I am also happy it works.
Is there a way to make this SUMMARIZE table dynamic? Because as soon as I put a filter on this chart, it still adds the 9bn, although the Total Sales values by month have changed.



P.S.: I already asked ChatGPT but he (she? it?) really got more stupid in recent months and couldn't help me so far 😉

[ToddChitt]

If I understand you correctly, you want to identify the Month and Year with the highest Sales (in your case Dec 2022 with 47Bn), add 20% to that (= 56 Bn) and plot THAT number across all months and years.

Instead of adding a CHART line, think about adding a CONSTANT Line. 

The trick is to get a single value in that option. 

You could do a DAX table that SUMMARIZES the sales, group by Year and Month and adds your 20%. Then set up a Constant Line based on this new table, and select the MAX aggregation of the [Monthly Total + 20%]:

My Constant Line = SUMMARIZE('Sales', 'Sales'[Month], 'Sales'[Year], "My Max", SUM('Sales'[Amount]) * 1.2 )

[scoobydoo]

Thank you, but I don't want to show the 56bn constantly. Instead I want the 20% to be constant, in this case 9bn, and this has to be added to every month. This would give me a line that runs parallel to the columns and thats what I need. Currently, the line doesn't run in parallel, because it calculates the 20% for every single month instead of using the 9bn constantly.

For example November 2022. It should be 18bn + 9bn = 27bn. But it is 18bn + 20% (of 18bn) = 22bn.

I am just a beginner and it makes me a little bit crazy, so any help would be appreciated!

[ToddChitt · Accepted solution]

OK, got it.

Use the SUMMARIZE table I suggested, but do the final math as " * 0.2" instead of " * 1.2".

That will generate a table of Month, Year, and a bunch of values, with the value for Dec 2022 being 9Bn.

Now add a MEASURE to that table:

My Target = MAX('My Table'[Value])

Nows, back in your original DAX function:

Sales MAX = 
    VAR _MaxSales = MAXX(ALL('Datetable'[MonthYear]), [Total Sales])
    VAR _Offset = [My Target]
    RETURN
        [Total Sales] + _Offset

[scoobydoo]

Thank you! I tried it and adjusted the measure to my actual table/column names. It now looks like this:

Sales + 20% = 
VAR _Table = SUMMARIZE('US_Data', 'Datetable US'[MonthYear], "20Percent", SUM(US_Data[Sales]) * 0.2)
VAR _Offset = MAXX(_Table,[20Percent])
   RETURN
   [Total Sales US] +  _Offset

But the chart still looks the same as before:

Did I do it the way you wanted? I'm still a beginner, so please have mercy with me and thanks for your help so far!

[foodd]

Please help @ToddChitt  out here, supply your work-in-progress Power BI Desktop file, as well as the DAX measures mentioned, and add an image of the model relationships view.  This will help him and other members of the forum who volunteer their time to help others.

[ToddChitt]

Can you show me the SUMMARIZE DAX function? And did you create a MEASURE on that table? doesn't look like it. And make sure there are NO relationships to this SUMMARIZE table.

[scoobydoo]

I didn't set it up as a "real" table, but put it in a variable inside of the measure (VAR _Table), because I already have a huge amount of data and wanted to save some space. Now I know, that this was obviously wrong (it's my first time using it) and created the table separately:

Sales + 20% Table = SUMMARIZE('US_Data', 'Datetable US'[MonthYear], "20Percent", SUM(US_Data[Sales]) * 0.2)

Then I calculated the measure upon this table:

Sales + 20% = 
VAR _Offset = MAX('Sales + 20% Table'[20Percent])
    RETURN
        [Total Sales US] + _Offset

... and it works! You're definetely my hero of the day, thank you so much for your great help.

[ToddChitt]

Thanks for the ego boost!

A couple of notes: 

For the Measure, a simple statement of 

Sales + 20% = MAX('Sales + 20% Table'[20Percent])

would have sufficed. No need to for the VAR and return statements.

Next, when setting up a SUMMARIZE function, you usually list columns in the same table as the first parameter:

SUMMARIZE ( 'Table 1', 'Table 1'[Grouping field]...

I don't usually see  

SUMMARIZE ( 'Table 1', 'Table 2'[Grouping field]...

But glad it worked.