Tutorial: Exporting and Downloading Calculated Data

Users can export data from the Control Chart XmR for Power BI into a file for further analysis or sharing. In this brief guide, we’ll walk you through the essential steps.

Try it yourself!

Here’s what you need to export and download calculated data from the Control Chart XmR:
– Enable the setting Allow downloads from custom visuals in the Admin Portal
– a Power BI report with your Dataset
Build and Format the Control Chart XmR
Save and Share

Enable Allow downloads from custom visuals in the Admin Portal

To manage Power BI visuals you must be a Global administrator or have been assigned the Fabric administrator role. Go to the Admin portal > Tenant settings, scroll down to Power BI visuals and enable Allow downloads from custom visuals. When this setting is enabled, users can download data from the Control Chart XmR into a file. It can export to files of the following types: .csv, .txt, .json, .tmplt, .xml, .pdf and .xlxs

  1. Expand the Allow downloads from custom visuals settings.
  2. Select Enabled.
  3. Choose who can download files:
    • Select The entire organization option to allow everyone in your organization to download data from a visual into a file.
    • Select the Specific security groups option to limit downloading files to specific security groups. Enter the security groups you want in the Enter security groups text bar. The security groups you specify are included by default. If you want to exclude these security groups and include everyone else in the organization, select the Except specific security groups option.
  4. Select Apply.

The UI tenant settings only affect the Power BI service. If you want these settings to take effect in Power BI Desktop, use AD Group Policy.

KeyValue nameValue
Software\Policies\Microsoft\Power BI Desktop\AllowCVToExportDataToFile0 – Disable
1 – Enable (Default)

When AllowCVToExportDataToFile is set to 1, the custom visual can export data to a file only if:

  • The feature switch in the admin portal is enabled.
  • The user is logged on.

Enable Allow export data in the Control XmR Chart

  1. Open your Power BI report and add a Control Chart XmR.
  2. Go to Format visual and enable Statistics per Rule.
  3. Expand the Calculation card and enable Allow export data.
  4. In the chart click Focus mode icon.
  5. Click the Download Data icon.
  6. The window Download from a custom visual? appears asking to confirm that the visual is from a trusted source.
  7. Click Download button.
  8. Rename and Save file.
  9. Open the file in Excel.
  10. Power BI exports the data to a (.csv) file. By default, your export is saved to your local Downloads folder.

Considerations and limitations

  • Allow downloads from custom visuals setting must be enabled by your Power BI administrator.
  • The API is supported only in the Power BI service and Power BI desktop.
  • The size limit for a downloaded file is 30 MB.

Tutorial: Setting up a Custom date scale

Adding a Custom date scale to your PowerGantt Chart

Now every user of the PowerGantt Chart can also define and include their own custom scale date. Adding a custom scale date to your PowerGantt Chart allows you to visualize project timelines and tasks more effectively. A custom scale date provides greater flexibility and precision in representing project schedules because there are unlimited number of custom date/time scales, like: fiscal years, special weeknumbers, AM/PM split of the day, etc..

In this article, you will find step-by-step instructions on how to achieve this.

Try it yourself!

Here’s what you need to define and set up a Custom scale date:
– a Power BI report with your Dataset (including task names, start dates, end dates, etc. )
– a Dataset with a date table
Link tables
Build and Format the PowerGantt Chart
Save and Share

1. Create a Date Table

A custom date table is essential for implementing a custom date scale. This table will include all the dates you want to display on the Power Gantt Chart. You can create a date table by using DAX functions or M Query.

1.1 Using DAX

  1. Click the Table view icon from the left menu or the Modeling tab from the top menu
  2. Click New Table
  3. Rename your new table
  4. Enter DAX expressions to create a date table and press Enter
  5. Optionally, select the Column and update the Format
  6. In the Data pane, right-click on it and Mark as date table.

1.2 Using M Query

  1. Click Table view icon from the left menu
  2. Click Get Data and select Blank query from the drop-down menu
  3. Select Advanced Editor
  4. Enter the code to create a date table and click Done
  5. Rename query
  6. Close & Apply.

If you add only a calendar table, convert it To Table in the Convert menu, click OK in the next menu (no selection needed). Rename the column to Date and right-click and select Change Type > Date.

You have created a date table, now you need to build a relationship between your dataset and the date table.

2. Link Tables

Connect the newly created date table with your existing dataset based on the date fields. You need to establish a relationship between the date table and the date fields in your dataset.

  1. Click Model view from the left menu
  2. Click on the Manage relationships from the top menu
  3. Click New…
  4. From the first drop-down, select your Dataset table and select a column with dates (for example the Start date)
  5. On the next drop-down, select the Date table you just created and select the Date column
  6. By default Cardinality is set to Many to one (*:1)
  7. By default the Cross filter direction is Single
  8. Press OK and Close buttons.

Your date table is now linked to your dataset.

3. Build visual

Add a PowerGantt Chart to your report and use the Custom scale date.

  1. In Power BI, open or locate the report with the PowerGantt visual
  2. Add categories to the fields (Item, Parent, Item Name, Start date, End date,…)
  3. Add the Date category to the Custom scale date
  4. Add categories to the Custom scale groups
  5. Right-click on each category in the Custom scale date and Custom scale group fields, make sure to click/select Don’t summarize and Show items with no data.

4. Format visual

Take advantage of the additional formatting features available for the Date scale in the formatting pane and customize it according to your preferences.

Go to Format visual pane and expand the Date scale card to:

  • Enable or disable the Date scale.
  • Show only groups in custom scale option.
  • Enable Overflow text.
  • Set a Start and End Date Range.
  • Style your date scale by setting the Font family, text Size and emphasized text (Bold, Italic, Underline).
  • Enable or disable the Today line and change the Style (solid, dotted, dashed), Color and Width of the line.

Additionally, change the Color and Background color of the Date scale under the Column headers card.

Save your Power BI report and publish it to the Power BI service.

Additional information

There are several reference materials and guides available for DAX and M Query, that can help you to define your custom date.

Data Analysis Expressions (DAX) Reference: https://learn.microsoft.com/en-us/dax/

DAX Guide: https://dax.guide/

CALENDAR DAX Function

Date = CALENDAR (DATE (2024, 1, 1), DATE (2024, 12, 31))

DAX Sample used in screenshots

DateTable = 
VAR StartDate = DATE(2024, 1, 1)
VAR EndDate = DATE(YEAR(TODAY()), 12, 31) 
RETURN
    ADDCOLUMNS (
        CALENDAR (StartDate, EndDate),
	"Year", YEAR([Date]),
        "QtrYr","Q" & FORMAT([Date], "Q YY"),
    	"Quarternumber", QUARTER([Date]),
    	"Month", FORMAT([Date], "MMMM"),
    	"Monthnumber", MONTH([Date]),
    	"MonthShortName", FORMAT([Date], "MMM"),
    	"Weekday", FORMAT([Date], "dddd"),
    	"Weeknumber", WEEKNUM([Date],1),
    	"WeekdayShortName", FORMAT([Date], "ddd"),
    	"DayOfWeek", WEEKDAY([Date]),
    	"WeekdayStartMonday", WEEKDAY([Date],2)
    )

Power Query M formula language: https://learn.microsoft.com/en-us/powerquery-m/

Power Query M formula language: https://learn.microsoft.com/en-us/powerquery-m/power-query-m-function-reference

M Query Samples

= List.Dates(#date(2024,1,1),365,#duration(1,0,0,0))

let
    Source = List.Dates(#date(2024,1,1),365,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Week of Year", "Day Name", each Date.DayOfWeekName([Date]), type text)
in
    #"Inserted Day Name"

Nova Silva Power BI Certified visuals: Ensuring Security and Privacy

In this article we will explore how using Power BI Certified visuals help organizations to protect data integrity and ensure the security and privacy of sensitive information.

What are Power BI Certified visuals?

Certified Power BI visuals are visuals in AppSource that meet certain specified code requirements that the Microsoft Power BI team has tested and approved. The tests are designed to check that the visual doesn’t access external services or resources, and that it follows secure coding patterns and guidelines. All certified visuals must pass all the certification requirements.

Why are Nova Silva visuals Power BI Certified?

All our Power BI visuals (including the Shielded HTML Viewer) are certified by Microsoft. At Nova Silva we develop visuals based on the Microsoft Certification requirements, so our visuals are tested to verify that they don’t access external services or resources, and that they follow secure coding patterns and guidelines. An important requirement to pass the Microsoft certification tests is that the visuals cannot connect with any external server or service. In other words: our visuals do not share any of your data with anyone, not even with us. This is the main reason why we certify all our visuals: we want to make sure you are the only party that can access your data.

When you use our visuals, you do not make use of any Nova Silva infrastructure or platform. Our visuals do not share data with any external service or resource, we have no access to any of your data, nor do we play a role in any production services. We publish our visuals (and any updates) to Microsoft. Microsoft will check our code to determine if it complies with their quality and security standards. After passing these certification tests, Microsoft will publish the visual to the Microsoft AppSource. This is where you will download and use it from. The next image gives an overview of how we work with Microsoft on our Power BI visuals.

The code of our visuals (at run time) are on a Microsoft managed environment. The only way we can change this code is through the Microsoft Certification process. Our visuals interact with Power BI through a Visuals API. This API provides the visual with the right data and context. The visual only renders the result on the screen within Power BI.

Microsoft Certification Process

This is the process to certify our visuals:

  • Comply with Microsoft’s Certification requirements.
  • Submit the Power BI visual for certification: visual is published via the Partner Center.
  • Certification Test: every time a new version of a certified visual is submitted to the marketplace, the visual’s version update goes under the same certification checks. The version update certification is automatic. If the update is rejected because of a violation, an email is sent to the developer explaining what needs to be fixed.
  • Publication timeline: the process of deploying to AppSource may take some time. The Power BI visual will be available to download from AppSource when this process is complete.

Each time we develop or update a visual we perform our own internal testing to make sure we comply with all Microsoft certification requirements. Then we publish the source code to Microsoft, and they perform their Certification Test. After passing all tests Microsoft will publish the visual to the Microsoft AppSource. This is the environment where our customers can access the visual and use it within their Power BI Environment.

Each time we publish a (new) version of our visual it first needs to pass the Microsoft Certification tests. This includes a thorough review of our source code by Microsoft engineers. A certified visual cannot lose its certification with a new update. Instead, the update is rejected. If the update is rejected because of a violation, an email is sent to Nova Silva explaining what needs to be fixed.

What are the requirements to get Power BI Certified?

These are some of the requirements to get a Power BI visual certified.

List of requirements:

  • Share the source code with Microsoft via private repository (with two-factor authentication)
  • Use the latest API version
  • Use ESLint with the Microsoft required security rules to scan errors and security violations
  • Use latest version of powerbi-visuals-tools to write the Power BI visual
  • 1200 Power BI visuals additional certification policies
  • Use only public reviewable OSS components
  • Support Rendering Events API
  • DOM is manipulated safely
  • Visual is approved for publishing in the AppSource
  • Do not pass or transmit customer data externally
  • Not accessing external services or resources by the visual

Before submitting a Power BI visual for certification:

To learn more about the certification process, go to Microsoft.

What tests are done during the certification process?

The certification process tests include, but aren’t limited to:

  • Code reviews
  • Static code analysis
  • Data leakage
  • Data fuzzing
  • Penetration testing
  • Access XSS testing
  • Malicious data injection
  • Input validation
  • Functional testing

What is not allowed?

  • Accessing external services or resources. For example, no HTTP/S or WebSocket requests can go out of Power BI to any services. Therefore, WebAccess privileges should be empty, or omitted, in the capabilities settings.
  • Using innerHTML, or D3.html(user data or user input).
  • JavaScript errors or exceptions in the browser console, for any input data.
  • Arbitrary or dynamic code such as eval(), unsafe use of settimeout()requestAnimationFrame()setinterval(user input function), and user input or user data.
  • Minified JavaScript files or projects.

How to identify Power BI Certified visuals in AppSource?

Certified visuals will be clearly identified by:

  • Filtering with Sort by: Power BI Certified to display only certified Power BI visuals in the AppSource.
  • With a small badge (yellow or blue checkmark) on the visual’s card, both in AppSource or when importing from within Power BI interface.
  • When the Power BI visual card is clicked in AppSource, a PBI Certified yellow badge and the message under Visual capabilities will indicate that This visual is certified by Power BI.

Non-certified visuals: some Power BI visuals aren’t certified because they don’t comply with one or more of the certification requirements. For example, a map Power BI visual connecting to an external service, or a Power BI visual using commercial libraries can’t be certified.

When that is the case the visual has no badge and will show this information:

Did you know?

Microsoft certified our first Power BI visual, the Shielded HTML Viewer, back in 2020! It was a long process and took several code reviews by Microsoft before we got our first visual certified.
Sometimes, users report the Shielded HTML Viewer does not work with LinkedIn or YouTube, our visuals do not render anything from any external server or service as this is one of the main requirements to be certified. This can only be achieved with a non-certified visual.
Currently all Nova Silva visuals are Power BI Certified, this guarantees our visuals are safe and secure and ensures they meet the highest data security standards.
We are committed to maintain those standards, all our visuals are either certified or in the process of getting certified.
Power BI certified visuals offer more features than non-certified visuals, like:
Export to PowerPoint
Export to PDF
– or display the visual in received emails when a user subscribes to report pages.

Tutorial: Purchasing, Assigning and Managing PER USER Licenses from Microsoft AppSource

Introduction

The PER USER license is sold directly through the Microsoft AppSource. These licenses can be purchased monthly/yearly. It supports the payment options you have with Microsoft for any Azure related purchases. For most organizations this is limited to credit card payments.

Every user, that is both Creator and Viewer need a valid license to use the visual. For example, if you have 2 users creating reports with the visual and 10 users who only consume the reports, you need: 2+10=12 user licenses. After you purchase these licenses from the Microsoft AppSource you need to assign the licenses to each user or user group. This is done at the Microsoft Admin Center.

Before purchasing a PER USER license through the Microsoft AppSource please read the Considerations below!

Considerations

The PER USER licenses can only be purchased directly by the organization that will use the visual and not via distributor/reseller.
There are currently no options to re-assign these licenses between organizations.

The best is that the tenant admin does the purchase (through the Microsoft AppSource).
If the tenant admin owns the licenses (Org licenses), then any of the license admins (Global Admin, License Admin or User Admin) can manage and assign the licenses.

Currently, the following Power BI environments don’t support PER USER license management or license enforcement:
• Embedded – Publish To Web (public URL) , PaaS embed
• National clouds (Depends on general support for transactability in national clouds)
• RS Server (No planned support)
• Exporting (PDF\PPT) using REST API
For unsupported environments you need a PREMIUM license.

The Nova Silva visuals you can download from the AppSource are Certified Power BI visuals that meet certain specified code requirements that the Microsoft Power BI team has tested and approved. The tests are designed to check that the visual doesn’t access external services or resources. Our visuals are updated automatically when a new version is available.

Purchasing PER USER Licenses

To purchase a Nova Silva visual through the Microsoft AppSource, click Buy now and complete the Checkout path. See below:

  1. Go to the Microsoft AppSource
  2. Click on the Nova Silva visual you want to purchase
  3. Optionally click the Plans + Pricing tab for an overview of the available plans and prices
  4. Click the Buy now button
  5. Checkout – Plan
    • Select a plan: Personal, Team, Departmental, Organizational or Corporate
    • Click Next button
  6. Checkout – Price + billing
    • Set Billing term to 1-month or 1-year subscription
    • Set Recurring billing: by default auto-renewal is On
    • Provide Number of users (both Creators and Viewers are considered Users)
    • Up next, choose your payment method: (you can pay with a credit card, prepaid cards are not supported)
    • Click Next button
  7. Checkout – Payment
  8. Review order and if applicable add Tax ID
    • Click Accept agreement and place order button to complete purchase.

You can add a purchase number to the billing profile of your order. At the final stage of your checkout, select Add new under the billing profile and add the Purchase order number in the field.

After you complete your purchase, you can find your receipt in the Microsoft 365 admin center by clicking Download invoice under Bills & payments > Invoices.

Billing and Global admins can also pay by invoice if that’s set up for your company. Read more about setting payment methods. Follow these steps to see your billing profile.

Assigning PER USER Licenses

After the purchase through Microsoft AppSource is complete and successful, the  buyer (owner) will need to assign the licenses to the users.

If the tenant admin owns the licenses (Org licenses), then any of the license admins (Global Admin, License Admin or User Admin) can manage the licenses.
However, if the subscription owner is non-admin, he will have access to the portal and only he can manage the subscription and licenses. The tenant admin can see all subscriptions purchased under the tenant account, including the subscriptions purchased by non-admins. You can assign licenses to users in the same tenant that you purchased subscription under regardless of the location.

  1. Go to admin.microsoft.com and select the Licenses link under the Billing node from the menu (a list of visuals appears)
  2. Click the Nova Silva visual name you want to assign licenses for
  3. In the next page, click + Assign licenses
  4. Search by name or email address
  5. Select the user(s) you want to assign the licenses to
  6. Click Assign button

Buyers get a license assigned to them automatically. It can take up to five minutes from the time of purchase for the license to take effect. If you want to add more users or update your subscription, go to the admin center. You can assign the license to the users directly by providing name or email, or through Microsoft Groups either Mail-enabled or Security Group.

If the owner of the subscription leaves the company, tenant admin purchases (typically known as organizational purchases) continue to work as-is. If the owner was an admin, any license admin (Global Admin, License Admin, User Admin) can continue to manage the licenses. The subscriptions can be managed by any Billing Admin or Global Admin with access to the billing account used for purchase.
If the owner wasn’t an admin, the license will continue to work as long as the subscription is active. However, the subscription can’t be managed by anyone else.

After you assign a license in the Microsoft 365 Admin Center, the license should be available within a few minutes.

If you were just assigned a license, refresh the report to activate it by doing one of the following:

  • If you’re using the licensed visual in the Power BI Service, refresh the report by hitting F5.
  • If you’re using the licensed visual in the Power BI Desktop, close and reopen it.

If, after you refresh, the license is still not available, wait a while and try again. It could take up to five minutes.

Managing PER USER Licenses

Access the information about the licenses you purchased and manage it in Your Products under the Billing node in the Microsoft 365 admin center.

Check the visual to activate the options or click the icons (Buy licenses or More actions). Here you can Buy licenses, Remove licenses, View billing profile, Edit recurring billing (to turn on/ off recurring bill) and Cancel subscription.

As a billing or global admin, you can see all subscriptions in the Microsoft 365 admin center, and you can cancel them.

In the admin center, go to Billing > Your products
Find and check the visual you want to manage
Click Cancel subscription
You’re eligible for a full refund if you cancel your subscription within seven days of purchase. Refunds aren’t available for subscriptions canceled after that time period. PER USER license purchases and refunds are handled by Microsoft and not by Nova Silva.

For help managing licenses, contact support or click the Help & Support option on the bottom right section of your screen. If you raise a support ticket provide the Subscription ID.

Considerations and limitations

Currently, the following Power BI environments don’t support PER USER license management or license enforcement:

  • Publish to web (P2W)
  • Sovereign or government clouds
  • RS (report server) – no Microsoft Entra ID
  • PaaS Power BI embedded App owns data
  • Exporting (PDF\PPT) using REST API

For unsupported environments you need a PREMIUM license, this can be purchased from our webshop.

Tutorial: Adding a PREMIUM License Manually or via a Theme file

This article provides a step-by-step guide for adding a license to our Power BI Premium visuals. You can license your visual in two ways:

  • Manually via de Power BI user interface or
  • Automatically via a Theme file.

Adding a License Manually

Follow these steps to license the visual in the Power BI user interface:

  1. In Power BI (Desktop), add the visual to your Power BI Report
  2. Click the Format your visual icon in the Visualizations pane or
  3. Locate and open the existing report with the visual
  4. Click the visual to activate the Format visual pane
  5. Expand the License card and paste the license information (you received per e-mail) into the License Name and License Key fields
  6. If the license information is entered correctly, you should see the expiry date of the license in the second field. Your visual is now successfully licensed!
  7. Save your changes and Publish to the service.

Adding a License via a Theme file

You can also add the license to a Theme.json file to avoid having to add it to each visual individually.

Power BI Themes are defined in so called JSON files. If you add your license information in a theme file it will apply the license to each visual within that report.

How to create or update and import a theme file:

  • Create a new JSON file or edit an existing one
  • Add the visualStyles values (see example below)
  • Save the .json file
  • In Power BI (Desktop), create a new report and add the visual or
  • Locate and open the existing report with the visual
  • Go to the View tab and click on the drop-down arrow
  • Choose Browse for themes
  • Locate and select your Theme.json file
  • Click the Open button to import it
  • A File successfully added dialog will show up
  • Click the Got it button
  • Your report book and all the pages will get the refreshed License
  • Save and Publish to the service.

If you already have a corporate Theme file, add the “visualStyles” properties and paste it to your Theme file.

License properties

Name on user interface

License Name

License Key

Name in Theme file

“licenseName”

“licenseKey”

The License Name and License Key are supplied to you specifically via e-mail. Add the following example to your Theme file and replace the text between quotes with your own license information.

"visualStyles": {
       "*": {
          "*": {
             "license": [{
                 "licenseName": "YOUR LICENSE NAME",
                 "licenseKey": "YOUR LICENSE KEY"
            }]
        }
    } 
}

Tutorial: how to add Power BI Analytic lines

Adding Analytic lines to a Dumbbell Bar

The Dumbbell Bar Chart supports Analytics features, you can now add dynamic reference lines to mark important trends or insights. The analytic lines are under the Analytics pane in the Visualizations area of Power BI Desktop or service (Edit mode).

The Analytics pane only appears when you select the visual on the Power BI canvas

How to: use Analytics

Each visual in Power BI has three different panes: Fields, Format and Analytics. The Analytics pane allows you to add reference lines to your visual (like: fixed value, median, average, max, etc.)

You can highlight interesting trends and insights by creating dynamic reference lines with the Analytics pane. To add further analyses to your visual, select the visual and click the Analytics icon (magnifying glass), turn On the line(s) and click to expand and format your line.

Available Analytic lines:

  • Constant line: displays the value specified, helps to track metrics and desired goals.
  • Min line: displays the lowest value points on the axis.
  • Max line: displays the highest value points on the axis.
  • Average line: displays the data average.
  • Median line: displays the middle value.
  • Percentile line: displays the value (or score) below which x% of the observations may be found.

Adding a Constant line

  1. Add a Dumbbell Bar Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Constant line
  4. Change Constant line from Off to On and click to expand
  5. Enter a Value
  6. Optionally set properties as desired (for formatting options, see section How to: format analytic lines)

Hover over image or click to maximize

Adding Min, Max lines

  1. Add a Dumbbell Bar Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Min line and/ or Max line
  4. Change Min line and/ or Max line from Off to On and click to expand
  5. Select Primary value or Secondary value from the Measure drop-down menu. By default the line is drawn on a Primary value
  6. Optionally set properties as desired

Adding an Average line

  1. Add a Dumbbell Bar Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Average line
  4. Change Average line from Off to On and click to expand
  5. Select Primary value or Secondary value from the Measure drop-down menu. By default the line is drawn on a Primary value
  6. Optionally set properties as desired

Adding a Median line

  1. Add a Dumbbell Bar Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Median line
  4. Change Median line from Off to On and click to expand
  5. Select Primary value or Secondary value from the Measure drop-down menu. By default the line is drawn on a Primary value
  6. Optionally set properties as desired

Adding a Percentile line

  1. Add a Dumbbell Bar Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Percentile line
  4. Change Percentile line from Off to On and click to expand
  5. Select Primary value or Secondary value from the Measure drop-down menu. By default the line is drawn on a Primary value
  6. Optionally set properties as desired

By default percentile is set to 90%, you can increase/ decrease the percentage value with the toggle bar or up/ down arrows.

Percentile Formula

We use the same formula as the PERCENTILE() function in Power BI
P = K * (n – 1) + 1
Where:
n = number of values in the data set
K = given percentile
Or
Percentile = Given percentile * (total number of values – 1) + 1
The percentile is defined as this: if K is the percentile, then K% of the values should be below it. For example, the 90th percentile is a value where 90 percent of the observations in the data set or group are below. This means the percentile is based on a data point.

How to: format analytic lines

Formatting options for analytic lines are in line with the options you are familiar with from Power BI. Theme colors and settings are also supported.

Once you add a line or multiple lines, you can change the color, transparency, width, style and position of each line. Enable the Data label to add a label next to the line and get additional options for your data label. You can edit the color, text, position and set the display units and decimal places.

Available formatting options:

  • Value* – This property is only available in a Constant line, here you can provide the value on the axis where the line will be rendered.
  • Percentile*- This property is only available in a Percentile line. By default 90%, you can increase/ decrease the value.
  • Measure* This property is available in visuals that support multiple measures such as the Lipstick and Dumbbell charts. Here you can add an analytic line to the Primary value or Secondary value. By default a line is set on a Primary value, click on the Measure drop-down menu to select the Secondary value.
  • Color – This property allows you to select a color for the line.
  • Transparency – This property determines how bright the line will appear on the visual. By decreasing transparency to zero, the line is a pure solid color line, increasing transparency to 100% will make the line invisible on the visual. By default transparency is set to 50%, so the line blends partially into the visual.
  • Line width – This property determines how thick the line will appear on the visual. By default is set to 3.
  • Line style – This property determines the style of the line as a continuous line ‘solid’, made up of dots ‘dotted’ or short strokes ‘dashed’. By default is dashed.
  • Position – This property allows you to set the position of the line: ‘In front’ or ‘Behind’ the bars. By default is In front.
  • Data Label Off – This property allows you to display text in the form of a data label for the line. By default, this property is set to ‘Off’.

Data label On – Change to ‘On’ to get options to format the data label.

  • Color – This property allows you to select a color for the data label.
  • Text – This property sets the value, name or both: ‘Data value’, ‘Name’, ‘Both’.
  • Horizontal position – This property sets the position of the data label: ‘Left’ or ‘Right’.
  • Vertical position – This property sets the position of the data label: ‘Above’ or ‘Under’.
  • Display units – This property determines the units: ‘Auto’, ‘None’, ‘Thousands’, ‘Millions’, ‘Billions’, ‘Trillions’. By default is Auto.
  • Value decimal places – This property allows you to set the number of decimals for the value. By default is Auto.

‘Reset to default’ or switch ‘Off’ to remove line and formatting options.

Try it yourself!

Do you want to try the Analytic lines in our visuals? Download any of the visuals available that support Analytics features from the Microsoft AppSource.

For any questions or remarks about this visual, please contact us by email at Nova Silva Support or visit our Community forum.

Tutorial: how to add Power BI Analytic lines

Adding Analytic lines to a Lipstick Bar

The Lipstick Bar Chart supports Analytics features, you can now add dynamic reference lines to mark important trends or insights. The analytic lines are under the Analytics pane in the Visualizations area of Power BI Desktop or service (Edit mode).

The Analytics pane only appears when you select the visual on the Power BI canvas

How to: use Analytics

Each visual in Power BI has three different panes: Fields, Format and Analytics. The Analytics pane allows you to add reference lines to your visual (like: fixed value, median, average, max, etc.)

You can highlight interesting trends and insights by creating dynamic reference lines with the Analytics pane. To add further analyses to your visual, select the visual and click the Analytics icon (magnifying glass), turn On the line(s) and click to expand and format your line.

Available Analytic lines:

  • Constant line: displays the value specified, helps to track metrics and desired goals.
  • Min line: displays the lowest value points on the axis.
  • Max line: displays the highest value points on the axis.
  • Average line: displays the data average.
  • Median line: displays the middle value.
  • Percentile line: displays the value (or score) below which x% of the observations may be found.

Adding a Constant line

  1. Add a Lipstick Bar Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Constant line
  4. Change Constant line from Off to On and click to expand
  5. Enter a Value
  6. Optionally set properties as desired (for formatting options, see section How to: format analytic lines)

Hover over image or click to maximize

Adding Min, Max lines

  1. Add a Lipstick Bar Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Min line and/ or Max line
  4. Change Min line and/ or Max line from Off to On and click to expand
  5. Select Primary value or Secondary value from the Measure drop-down menu. By default the line is drawn on a Primary value
  6. Optionally set properties as desired

Adding an Average line

  1. Add a Lipstick Bar Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Average line
  4. Change Average line from Off to On and click to expand
  5. Select Primary value or Secondary value from the Measure drop-down menu. By default the line is drawn on a Primary value
  6. Optionally set properties as desired

Adding a Median line

  1. Add a Lipstick Bar Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Median line
  4. Change Median line from Off to On and click to expand
  5. Select Primary value or Secondary value from the Measure drop-down menu. By default the line is drawn on a Primary value
  6. Optionally set properties as desired

Adding a Percentile line

  1. Add a Lipstick Bar Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Percentile line
  4. Change Percentile line from Off to On and click to expand
  5. Select Primary value or Secondary value from the Measure drop-down menu. By default the line is drawn on a Primary value
  6. Optionally set properties as desired

By default percentile is set to 90%, you can increase/ decrease the percentage value with the toggle bar or up/ down arrows.

Percentile Formula

We use the same formula as the PERCENTILE() function in Power BI
P = K * (n – 1) + 1
Where:
n = number of values in the data set
K = given percentile
Or
Percentile = Given percentile * (total number of values – 1) + 1
The percentile is defined as this: if K is the percentile, then K% of the values should be below it. For example, the 90th percentile is a value where 90 percent of the observations in the data set or group are below. This means the percentile is based on a data point.

How to: format analytic lines

Formatting options for analytic lines are in line with the options you are familiar with from Power BI. Theme colors and settings are also supported.

Once you add a line or multiple lines, you can change the color, transparency, width, style and position of each line. Enable the Data label to add a label next to the line and get additional options for your data label. You can edit the color, text, position and set the display units and decimal places.

Available formatting options:

  • Value* – This property is only available in a Constant line, here you can provide the value on the axis where the line will be rendered.
  • Percentile*- This property is only available in a Percentile line. By default 90%, you can increase/ decrease the value.
  • Measure* This property is available in visuals that support multiple measures such as the Lipstick and Dumbbell charts. Here you can add an analytic line to the Primary value or Secondary value. By default a line is set on a Primary value, click on the Measure drop-down menu to select the Secondary value.
  • Color – This property allows you to select a color for the line.
  • Transparency – This property determines how bright the line will appear on the visual. By decreasing transparency to zero, the line is a pure solid color line, increasing transparency to 100% will make the line invisible on the visual. By default transparency is set to 50%, so the line blends partially into the visual.
  • Line width – This property determines how thick the line will appear on the visual. By default is set to 3.
  • Line style – This property determines the style of the line as a continuous line ‘solid’, made up of dots ‘dotted’ or short strokes ‘dashed’. By default is dashed.
  • Position – This property allows you to set the position of the line: ‘In front’ or ‘Behind’ the bars. By default is In front.
  • Data Label Off – This property allows you to display text in the form of a data label for the line. By default, this property is set to ‘Off’.

Data label On – Change to ‘On’ to get options to format the data label.

  • Color – This property allows you to select a color for the data label.
  • Text – This property sets the value, name or both: ‘Data value’, ‘Name’, ‘Both’.
  • Horizontal position – This property sets the position of the data label: ‘Left’ or ‘Right’.
  • Vertical position – This property sets the position of the data label: ‘Above’ or ‘Under’.
  • Display units – This property determines the units: ‘Auto’, ‘None’, ‘Thousands’, ‘Millions’, ‘Billions’, ‘Trillions’. By default is Auto.
  • Value decimal places – This property allows you to set the number of decimals for the value. By default is Auto.

‘Reset to default’ or switch ‘Off’ to remove line and formatting options.

Try it yourself!

Do you want to try the Analytic lines in our visuals? Download any of the visuals available that support Analytics features from the Microsoft AppSource.

For any questions or remarks about this visual, please contact us by email at Nova Silva Support or visit our Community forum.

Tutorial: how to add Power BI Analytic lines

Adding Analytic lines to a Dumbbell Column

The Dumbbell Column Chart supports Analytics features, you can now add dynamic reference lines to mark important trends or insights. The analytic lines are under the Analytics pane in the Visualizations area of Power BI Desktop or service (Edit mode).

The Analytics pane only appears when you select the visual on the Power BI canvas

How to: use Analytics

Each visual in Power BI has three different panes: Fields, Format and Analytics. The Analytics pane allows you to add reference lines to your visual (like: fixed value, median, average, max, etc.)

You can highlight interesting trends and insights by creating dynamic reference lines with the Analytics pane. To add further analyses to your visual, select the visual and click the Analytics icon (magnifying glass), turn On the line(s) and click to expand and format your line.

Available Analytic lines:

  • Constant line: displays the value specified, helps to track metrics and desired goals.
  • Min line: displays the lowest value points on the axis.
  • Max line: displays the highest value points on the axis.
  • Average line: displays the data average.
  • Median line: displays the middle value.
  • Percentile line: displays the value (or score) below which x% of the observations may be found.

Adding a Constant line

  1. Add a Dumbbell Column Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Constant line
  4. Change Constant line from Off to On and click to expand
  5. Enter a Value
  6. Optionally set properties as desired (for formatting options, see section How to: format analytic lines)

Hover over image or click to maximize

Adding Min, Max lines

  1. Add a Dumbbell Column Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Min line and/ or Max line
  4. Change Min line and/ or Max line from Off to On and click to expand
  5. Select Primary value or Secondary value from the Measure drop-down menu. By default the line is drawn on a Primary value
  6. Optionally set properties as desired

Adding an Average line

  1. Add a Dumbbell Column Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Average line
  4. Change Average line from Off to On and click to expand
  5. Select Primary value or Secondary value from the Measure drop-down menu. By default the line is drawn on a Primary value
  6. Optionally set properties as desired

Adding a Median line

  1. Add a Dumbbell Column Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Median line
  4. Change Median line from Off to On and click to expand
  5. Select Primary value or Secondary value from the Measure drop-down menu. By default the line is drawn on a Primary value
  6. Optionally set properties as desired

Adding a Percentile line

  1. Add a Dumbbell Column Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Percentile line
  4. Change Percentile line from Off to On and click to expand
  5. Select Primary value or Secondary value from the Measure drop-down menu. By default the line is drawn on a Primary value
  6. Optionally set properties as desired

By default percentile is set to 90%, you can increase/ decrease the percentage value with the toggle bar or up/ down arrows.

Percentile Formula

We use the same formula as the PERCENTILE() function in Power BI
P = K * (n – 1) + 1
Where:
n = number of values in the data set
K = given percentile
Or
Percentile = Given percentile * (total number of values – 1) + 1
The percentile is defined as this: if K is the percentile, then K% of the values should be below it. For example, the 90th percentile is a value where 90 percent of the observations in the data set or group are below. This means the percentile is based on a data point.

How to: format analytic lines

Formatting options for analytic lines are in line with the options you are familiar with from Power BI. Theme colors and settings are also supported.

Once you add a line or multiple lines, you can change the color, transparency, width, style and position of each line. Enable the Data label to add a label next to the line and get additional options for your data label. You can edit the color, text, position and set the display units and decimal places.

Available formatting options:

  • Value* – This property is only available in a Constant line, here you can provide the value on the axis where the line will be rendered.
  • Percentile*- This property is only available in a Percentile line. By default 90%, you can increase/ decrease the value.
  • Measure* This property is available in visuals that support multiple measures such as the Lipstick and Dumbbell charts. Here you can add an analytic line to the Primary value or Secondary value. By default a line is set on a Primary value, click on the Measure drop-down menu to select the Secondary value.
  • Color – This property allows you to select a color for the line.
  • Transparency – This property determines how bright the line will appear on the visual. By decreasing transparency to zero, the line is a pure solid color line, increasing transparency to 100% will make the line invisible on the visual. By default transparency is set to 50%, so the line blends partially into the visual.
  • Line width – This property determines how thick the line will appear on the visual. By default is set to 3.
  • Line style – This property determines the style of the line as a continuous line ‘solid’, made up of dots ‘dotted’ or short strokes ‘dashed’. By default is dashed.
  • Position – This property allows you to set the position of the line: ‘In front’ or ‘Behind’ the bars. By default is In front.
  • Data Label Off – This property allows you to display text in the form of a data label for the line. By default, this property is set to ‘Off’.

Data label On – Change to ‘On’ to get options to format the data label.

  • Color – This property allows you to select a color for the data label.
  • Text – This property sets the value, name or both: ‘Data value’, ‘Name’, ‘Both’.
  • Horizontal position – This property sets the position of the data label: ‘Left’ or ‘Right’.
  • Vertical position – This property sets the position of the data label: ‘Above’ or ‘Under’.
  • Display units – This property determines the units: ‘Auto’, ‘None’, ‘Thousands’, ‘Millions’, ‘Billions’, ‘Trillions’. By default is Auto.
  • Value decimal places – This property allows you to set the number of decimals for the value. By default is Auto.

‘Reset to default’ or switch ‘Off’ to remove line and formatting options.

Try it yourself!

Do you want to try the Analytic lines in our visuals? Download any of the visuals available that support Analytics features from the Microsoft AppSource.

For any questions or remarks about this visual, please contact us by email at Nova Silva Support or visit our Community forum.

Tutorial: how to add Power BI Analytic lines

Adding Analytic lines to a Lipstick Column

The Lipstick Column Chart supports Analytics features, you can now add dynamic reference lines to mark important trends or insights. The analytic lines are under the Analytics pane in the Visualizations area of Power BI Desktop or service (Edit mode).

The Analytics pane only appears when you select the visual on the Power BI canvas

How to: use Analytics

Each visual in Power BI has three different panes: Fields, Format and Analytics. The Analytics pane allows you to add reference lines to your visual (like: fixed value, median, average, max, etc.)

You can highlight interesting trends and insights by creating dynamic reference lines with the Analytics pane. To add further analyses to your visual, select the visual and click the Analytics icon (magnifying glass), turn On the line(s) and click to expand and format your line.

Available Analytic lines:

  • Constant line: displays the value specified, helps to track metrics and desired goals.
  • Min line: displays the lowest value points on the axis.
  • Max line: displays the highest value points on the axis.
  • Average line: displays the data average.
  • Median line: displays the middle value.
  • Percentile line: displays the value (or score) below which x% of the observations may be found.

Adding a Constant line

  1. Add a Lipstick Column Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Constant line
  4. Change Constant line from Off to On and click to expand
  5. Enter a Value
  6. Optionally set properties as desired (for formatting options, see section How to: format analytic lines)

Hover over image or click to maximize

Adding Min, Max lines

  1. Add a Lipstick Column Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Min line and/ or Max line
  4. Change Min line and/ or Max line from Off to On and click to expand
  5. Select Primary value or Secondary value from the Measure drop-down menu. By default the line is drawn on a Primary value
  6. Optionally set properties as desired

Adding an Average line

  1. Add a Lipstick Column Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Average line
  4. Change Average line from Off to On and click to expand
  5. Select Primary value or Secondary value from the Measure drop-down menu. By default the line is drawn on a Primary value
  6. Optionally set properties as desired

Adding a Median line

  1. Add a Lipstick Column Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Median line
  4. Change Median line from Off to On and click to expand
  5. Select Primary value or Secondary value from the Measure drop-down menu. By default the line is drawn on a Primary value
  6. Optionally set properties as desired

Adding a Percentile line

  1. Add a Lipstick Column Chart to your Power BI Report
  2. Select the visual
  3. Go to Visualizations > Analytics > Percentile line
  4. Change Percentile line from Off to On and click to expand
  5. Select Primary value or Secondary value from the Measure drop-down menu. By default the line is drawn on a Primary value
  6. Optionally set properties as desired

By default percentile is set to 90%, you can increase/ decrease the percentage value with the toggle bar or up/ down arrows.

Percentile Formula

We use the same formula as the PERCENTILE() function in Power BI
P = K * (n – 1) + 1
Where:
n = number of values in the data set
K = given percentile
Or
Percentile = Given percentile * (total number of values – 1) + 1
The percentile is defined as this: if K is the percentile, then K% of the values should be below it. For example, the 90th percentile is a value where 90 percent of the observations in the data set or group are below. This means the percentile is based on a data point.

How to: format analytic lines

Formatting options for analytic lines are in line with the options you are familiar with from Power BI. Theme colors and settings are also supported.

Once you add a line or multiple lines, you can change the color, transparency, width, style and position of each line. Enable the Data label to add a label next to the line and get additional options for your data label. You can edit the color, text, position and set the display units and decimal places.

Available formatting options:

  • Value* – This property is only available in a Constant line, here you can provide the value on the axis where the line will be rendered.
  • Percentile*- This property is only available in a Percentile line. By default 90%, you can increase/ decrease the value.
  • Measure* This property is available in visuals that support multiple measures such as the Lipstick and Dumbbell charts. Here you can add an analytic line to the Primary value or Secondary value. By default a line is set on a Primary value, click on the Measure drop-down menu to select the Secondary value.
  • Color – This property allows you to select a color for the line.
  • Transparency – This property determines how bright the line will appear on the visual. By decreasing transparency to zero, the line is a pure solid color line, increasing transparency to 100% will make the line invisible on the visual. By default transparency is set to 50%, so the line blends partially into the visual.
  • Line width – This property determines how thick the line will appear on the visual. By default is set to 3.
  • Line style – This property determines the style of the line as a continuous line ‘solid’, made up of dots ‘dotted’ or short strokes ‘dashed’. By default is dashed.
  • Position – This property allows you to set the position of the line: ‘In front’ or ‘Behind’ the bars. By default is In front.
  • Data Label Off – This property allows you to display text in the form of a data label for the line. By default, this property is set to ‘Off’.

Data label On – Change to ‘On’ to get options to format the data label.

  • Color – This property allows you to select a color for the data label.
  • Text – This property sets the value, name or both: ‘Data value’, ‘Name’, ‘Both’.
  • Horizontal position – This property sets the position of the data label: ‘Left’ or ‘Right’.
  • Vertical position – This property sets the position of the data label: ‘Above’ or ‘Under’.
  • Display units – This property determines the units: ‘Auto’, ‘None’, ‘Thousands’, ‘Millions’, ‘Billions’, ‘Trillions’. By default is Auto.
  • Value decimal places – This property allows you to set the number of decimals for the value. By default is Auto.

‘Reset to default’ or switch ‘Off’ to remove line and formatting options.

Try it yourself!

Do you want to try the Analytic lines in our visuals? Download any of the visuals available that support Analytics features from the Microsoft AppSource.

For any questions or remarks about this visual, please contact us by email at Nova Silva Support or visit our Community forum.