Power BI has 5 different ways of positioning data labels in a bar chart – Auto, Inside end, Outside end, Inside center and inside base.
Depending on the distribution of your data this can lead to labels are being hidden, cut off or positioned a way that looks strange for the user.
In the first visual I have modified the visual in order to control the position as the base and made the numbers visible for all the categories – this can be compared to the last where the position inside base is positioned.
In this case I find this way much better but how did I do it.
Let’s go through the process
We start by creating a clustered bar chart where we add Category to the y-axis and Sales Value on the x-axis.
If we just use the standard data labels settings we won’t be able to get the result so lets add a visual calculation that matches the highest value in the visual.
So click on add data in the x-axis field and click the visual calculation icon
This will open the pane where we can add a calculation and create this calculation. This will calculate the highest Sales Value for the Category fieldn
This will give us a placeholder that we can use for the data label.
So next thing is to change the formatting of the bars.
The layout for all series should then be changed to 100 % space between series and turn on Overlap.
This will position our Max Category Value on top of all the bars we want to show.
So select the Max Category Value series under bars and set the transparency to 100 %
We are getting closer to let’s turn on data labels
Turn the labels off for Sales Value
And then modify the data labels for the Max Category Value series to use the Field Sales Value in the Value property and position the label as “Inside base”
And now the rest is formatting the number format and make sure your combination of label color and bar color is good.
In this example I have added title, subtitle, added divider to titles, removed legend and turned the values of for the x-axis.
With the introduction of visual calculation in the February 2024 release of Power BI desktop (https://powerbi.microsoft.com/en-us/blog/visual-calculations-preview/) – this gives us some new possibilities to add calculations on the individual visual and some new functions gives us some exiciting options.
One example could be to use the MOVINGAVERAGE function (link) to and combine it with numeric range parameter to make it dynamic.
Let me take you through how this can be done
As an example file I have used the datagoblins contoso file sample file that you can download from here (link)
I have added a measure for Sales Value and some time intelligence function to filter the development chart for the selected time intelligence period.
Let’s start by adding a moving average with a visual calculation – If you have enabled the preview feature via options you should see the “New calculation” button enabled in the ribbon.
When you click this the Calculation editor will appear
If change the name and click the fx – you can select a template called Moving average
This will add the formula
And you can modify it to this
The field refers to the measure you want to calculate the average for and the windowSize refers to the number of rows to include in the calculation. In my example I have week numbers on rows and I want a moving average for the last 13 weeks.
Be aware that the documentation describes the optional arguments as well – click on the image to see the official documentation.
When committing the formula you will see the result
And going back do the report we now have a line showing the moving average for Sales Value for the last 13 weeks … 👍
But what if the user wants a little more control over the length of the period ??
My first thought was – let’s just add a slicer with a numeric range parameter – So I clicked the Numeric range under New Parameter
And created it like this
And with some formatting I ended with this slicer
And in the model we can refer to the value of the parameter via created measure “Moving Average Value”
Next step is to modify the visual calculation
Then I thought lets replace the constant value with the parameter measure value
But in the intellisense I found I couldn’t refer to all measures in the model only the add fields was visible
Okay – lets see if we can cheat – so I added the parameter value field as a tooltip
This meant that I was able to refer to it …
But only to be hit be the constraint in the function that the window size must be a constant value
hmmmm… what to do ?? – So I decided to make the solution a bit simpler…
First I changed the parameter table constructor from 1-26
to only 4 different values
And then modified the slicer to the new slicer and added some formatting and placed it on top of the development chart.
So when ever the user picks another value in the slicer the switch statement will evaluate the current value of the Moving Average Value – and use the appropriate constant value,
And with some further formatting and subtitle we end up with this
The introduction of the DAX query view (official blogpost) and the new INFO DAX functions (official blogpost) gives us some very possibilities to update our measures without using the formula bar.
We can for instance create a query that shows all the measures that has an error message like this
EVALUATE VAR _X =INFO.MEASURES() RETURN FILTER(_X, [ErrorMessage]<>"")
This will extract all my measures in the open power bi file that contains an error message.
In this case I have a measure that contains an error message telling me that I can’t use a True/False expression to filter a table. Normally you would probably search for the measure and the field list and modify via the formula bar but there is another way and will make it possible to test it before you update it.
Let me show you how 😀
The INFO.Measures() also returns the expression for the measure
Select the expression and press CTRL+C to copy the expression
Then go to the DAX query and type in DEFINE followed by a line break and then use intellisense to find the Measure and paste the copied expression – this look something like this
As soon as you start to modify the expression of the measure the text “Update model: Overwriting measure” will appear
And if you want to test it before you hit the update model “button” – you can add an EVALUATE statement and see if the measure works as intended.
When you click to update – you will get a warning that tells you that there is no way to Undo it – so be sure 🙂
Hope you this can help you and if you like it – please give the post a like 🙂
Last week I found a nice little hack that enables us to create a dynamic slicer with one or more levels and let the user pick the different levels in the slicer.
This has been very useful in one of our solutions for our clients where it can limit the number of slicers needed in a report.
First we will create a field parameter in our model where we add the different fields we want to be able to slice on
From the product table I select different fields and place them in the order I would like them sorted.
Just leave the “Add slicer to this page” on to add a slicer by default and the slicer looks like this.
Now add a table to the page and add the field parameter as the columns field.
As we haven’t selected any fields from the slicer, we will see all the different values in the fields.
Now change the visual to a slicer (not the new button slicer) and notice what happens.
The visual is now a slicer containing the values from the different fields.
And you can now select the fields you want to have in the slicer
And if you want them in another ordinal level you select the first level and then CTRL click the next the level – in this example Class and then Category
And notice the slicer header is automatically populated with the field names
Quick Power BI tip here on a wonderful Monday morning – that I just discovered.
It’s possible to use a image URL as your background to your canvas – I used to think that this was only possible via a Power BI theme file – but no… that’s not the case.
I am using Power BI Desktop Version: 2.121.644.0 64-bit (september 2023).
Here is how…
In the format pane find the “Canvas background”
In the Image property – click the browse icon
And as the file name paste in the image URL – make sure the url returns an image
Just a quick post on how you can control the sort order when using a field parameter as value in a visual in Power BI.
In my scenario I have created a field parameter to choose between showing Sales Value and Sales Units
And now I can use this as the y-axis in a column chart.
And let the user select whether to show Sales Value or Sales Units.
The column chart only has one field on the x-axis – but if I add more than one field or a hierarchy the sorting won’t work as you might wish for.
As soon as we add another level – the sorting will be default jump to sorting by the level we have drilled too in the visual
Now sorted by chain in alphabetical order
Now we can change it manually so a sort by the current selection of field parameter.
But if we change the field parameter to show Sales Units – the visual will jump back to sorting alphabetically by the selected level on the x-axis.
This might be the default way of doing it but if you always want the chart to be sorted by a particular measure even though you are showing another measure then you can use this workaround. You should of course inform the user about the chosen sort order for instance in the subtitle or similar.
WORKAROUND
Let’s say I always want to sort the chart after Sales Value – then I can add the measure as a tooltip to the visual.
All the different fields you add as tooltips can also be used to sort the visual by
And now the sorting will always happen on the selected tooltip value instead of the field on the x-axis.
Here we show the Sales Value
Remember – This workaround is only relevant when you have more than one field on the x-axis – but we typically do for drilling up and down so hopefully this little tip can help you two.
In the June 23 release of Power BI desktop we has been blessed with a new card visual that not only will give a better performance compared to adding multiple cards in the past but it also gives us some formatting options we have been dreaming of for a long time.
On social media you have probably already seen a lot of examples of usages, and I wanted to share how you can add a sparkline to the card to make it look something like this.
When first added you might think … hmmm that is not really a big difference – BUT the BIG difference lies in your formatting options and that you can add more than one measure to the visual.
With the old card you would have to add a second Card to your report and sending two queries to your dataset instead of 1 as the new visual will do. Better performance – YES thank you.
In this post I will focus on how we can use the formatting options to make it really stand out.
Formatting options
Each of the measures you add to the visual can be formatted individually via the Cards properties
Let’s start by adding an accent bar to the left
Tip – Notice that the color can be controlled by fx meaning that you could make the accent color change depending whether the temperature was higher or lower compared to last year.
Another way of adding an accent bar is to use an image via the Fill properties
And then via the image control the location of your accent bar – the image is however static and can’t be controlled by a formula.
The values and label is formatted via the Callout section of the visual
Here you should especially notice that we now can control how blank values should be shown ️
Here is the default way
And here telling the user that there is no data
The label can also be controlled by an expression –
Giving us the possibility to give feed back to the reader whether the temp is higher or lower than last year for instance.
Here is the DAX measure linked to the label
Feedback on temp = IF([Avg Temperature] > [Avg Temperature LY] , "Avg temp - higher than last year", "Avg temp - lower than last year")
But now it’s time to look at how to add a sparkline
The property we have to use to do this is the Image property in the Cards section.
The Image type can be two different types – either an image or an image URL
When you pick the image URL
Notice that it can be a static URL or based on an expression
So I connect the Image URL to my Field called Temperature Sparkline
OBS – Remember to set the Category of your expression to Image URL
OBS – One thing I have noticed is that I have had to change the Size from Auto to a particular px size for it to work.
And now the card has a sparkline
In my formula I have also added the change value compared to last year average temperature.
So how did I make the formula to create the sparkline
I took a look at Kerry Kolosko amazing templates for SVG’s
The measures can/should be modified to fit your column names and measures and could be made even more dynamic using field parameters – but that might come in a later blogpost.
Yesterday I worked on a Power BI report where the client wanted to add vertical bar next to the slicer in order to make the design of the slicers a little bit nicer and also to make the slicers recognizable cross pages by using the same coloring.
My first choice was to use a shape and group it together with the slicer
But there is an easier way.
Use the Shadow properties of the slicer.
If you choose the Custom Position
You set the
Size to 0 px
Blur to 0 px
Angle to 180 (if you want the bar to the right of the slicer you can specify 0)
Distance 10 px (determines the width of the bar)
Transparency 0 px
These settings will set the slicer to look like this
And you avoid having shapes and grouping on your page
If you change the slicer to a tile or vertical list the bar will also be there
I thought it would be nice if I could add this as a description to my tables in Power BI model.
How hard can that be 🙂
Well, it turns out that we are not allowed to modify the table object via the Tabular #ditor and this includes adding a description to the table
So, if I created a script that added a description – it worked fine but returning to the desktop I was asked to refresh the queries and the descriptions where removed by Power BI desktop –
Well, what is supported as write operations to a model?
Calculation groups is supported – hmmm… could I use this and then actually create a calculation group with a calculation item for each of the tables in my model – that would also mean that I could create a table in my report and expose the descriptions in a report page instead of the description should be hidden away on the tooltip of a table in the field list – This could actually be quite awesome!
C# Script here we go
I know very little about how to write C# code so Darrens example and the documentation of Tabular editor was a big help and please bear in mind that I am a C# novice when reading my code
The steps needed to this is as follows (I have used Tabular Editor 3 but it should be possible to us the free version of Tabular Editor as well
Step 1 – Open your Power BI desktop file and find the external tool ribbon and click the Tabular Editor
Step 2 – Add a Calculation Group called “Documentation”
Step 3 – Choose to create a New C# Script
Step 3 – Use the following script and run it
#r "System.Net.Http"
using System.Net.Http;
using System.Text;
using Newtonsoft.Json.Linq;
// You need to signin to https://platform.openai.com/ and create an API key for your profile then paste that key
// into the apiKey constant below
const string apiKey = "<YOUR_API_KEY>";
const string uri = "https://api.openai.com/v1/completions";
const string question = "Please describe this power query code for me:\n\n";
using (var client = new HttpClient()) {
client.DefaultRequestHeaders.Clear();
client.DefaultRequestHeaders.Add("Authorization", "Bearer " + apiKey);
foreach (var t in Model.Tables)
{
foreach ( var p in t.Partitions)
{
// Only uncomment the following when running from the command line or the script will
// show a popup after each measure
//
//var body = new requestBody() { prompt = question + m.Expression };
string _type = Convert.ToString(p.SourceType);
string _exp = Convert.ToString(p.Expression);
if ( _type == "M" )
{var body =
"{ \"prompt\": " + JsonConvert.SerializeObject( question + p.Expression ) +
",\"model\": \"text-davinci-003\" " +
",\"temperature\": 1 " +
",\"max_tokens\": 256 " +
",\"stop\": \".\" }";
var res = client.PostAsync(uri, new StringContent(body, Encoding.UTF8,"application/json"));
//res.Result.EnsureSuccessStatusCode();
var result = res.Result.Content.ReadAsStringAsync().Result;
var obj = JObject.Parse(result);
var desc = obj["choices"][0]["text"].ToString().Trim();
//Reference to your calculation group that should hold the calculation Items
var x =(Model.Tables["Documentation"] as CalculationGroupTable).CalculationItems[t.Name];
//deletes the old version
x.Delete();
var calculationItem1 = (Model.Tables["Documentation"] as CalculationGroupTable).AddCalculationItem();
//removes any quotes in the chatGPT description
var s = desc.Replace("\"", "");
calculationItem1.Expression = "\"" + s + "\"";
calculationItem1.Name = t.Name;
//Info("Processing " + t.Name) ;
}
}
}
}
This will create a calculation item for each of your Power Query table and add a Expression that contains the chatGPT description of your M code
Example of one of the tables
Step 4 – Save the changes back to your model and you will be prompted to refresh your calculation group
Step 5 – Add a table where you take the Calculation group name and a measure that I call Query description – the value of this measure will in the table be changed to the expression of the calculation item.
We could use the same method to document our DAX measures and put that into a calculation group as well – thereby documenting our full model and exposing the information directly in the report and not just in the tooltips of the fields.
I will try to find time to do a blog post on this as well.
Let me know in the comments if you find this useful – would very much like to hear from you.
Yesterday I posted an image of some PowerPoint VBA code on LinkedIn and Twitter and a lot of people have asked for a copy of the code.
So I have made a Power Point file with the code included and a small instruction on how to use it.
The code has been changed compared to the posted image as it ran to fast for Power Point – so I added a pause in the code to wait 1 second for each conversion.
The Code
Sub ConvertAllPBIToImages() Dim x As Slide Dim shp As Shape
Dim sShapes As Shapes
'Loop through all the slides
For Each x In ActivePresentation.Slides
x.Select
'Loop through all the shapes on the slide
For Each shp In x.Shapes
'Is it a Power BI App - Report
If shp.Title = "Microsoft Power BI" Then
'Inserted to make sure as the code sometimes runs
'a bit to fast for Power Point
WAIT = Timer
While Timer < WAIT + 2
DoEvents 'do nothing
Wend
shp.Copy
Set sShapes = x.Shapes
x.Shapes.PasteSpecial ppPasteBitmap
shp.Delete
End If
Next
Next