cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

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

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!

1 ACCEPTED SOLUTION
Super User

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

 Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!

9 REPLIES 9
Helper I

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 😉

Super User

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.

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 )

 Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!

Helper I

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!

Super User

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

 Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!

Helper I

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!
Super User

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.

Super User

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.

 Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!

Helper I

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.

Super User

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]...

 Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors