Data Model Options for Power BI Solutions

At the heart of every a Business Intelligence reporting  solution is a data model, to optimize queries and enable ad hoc report interactions.  Data modeling technology has evolved quite a lot over the past 20 years or so.  You have several choices and options depending on the simplicity or formality of your project, and factors like data size and security.  In the past, choices were simpler.  Power BI was the choice for smallish, “good enough” projects and when data quality, high volume and exactness were the priority, SSAS was the better choice.  Now, using Power BI for modelling larger data sets is even advantageous with new features like hybrid models, aggregations and incremental data refresh.

I’ve been a quest to find the best medium to break these choices down into simple terms but it truly is a journey and not a destination.

Earlier this year, I presented a session called “The Nine Realms of Power BI” where I enumerated different categories of solution architectures for Power BI solutions; and they are numerous.  Just in the past year, so o optimize queries & support self-service reporting.  have been made – and are in the process of being added – to the Power BI platform, that the list of options and considerations continue to grow.

One important consideration is Microsoft’s commitment to support a product with new features in the future.  They have made it very clear that the Power BI platform is their primary focus and that they will continue to invest in enterprise-scale capabilities in the cloud service.  Never plan on a crucial feature being added later to a product but give serious consideration to  where a product is headed.

Making a side-by-side comparison of features between products and options is a little like comparing oranges, apples, grapes and bananas.  As a best effort, I started with the feature lists for SQL Server Analysis Services and added the Power BI variations.  Admittedly, this type of comparison doesn’t fit perfectly into this type of pivot format but I think it serves the purpose.  This post will likely evolve a bit with time.  Your feedback and input are welcome.

open/download Excel file  (site theme doesn’t currently support wide tables… working on that)

Feature
Enterprise/Developer
StandardAzure Analysis ServicesPower BI StandardPBI Report ServerPower BI PremiumComments
Max model size (compressed)No limit*16 GB No limit*1 GB2 GB10-12 GB**Premium supports 10 GB upload & 12 GB refresh.
Hybrid models
(DirectQuery & cached mode)
NoNoNoNoNoYes 
M/Power Query sources & transformationsYesYesYesYesYesYes**Query extensions in the service using dataflows
Integrated application lifecycle management (ALM) toolsYesYesYesNoNoNo 
Integrated version control toolsYesYesYesNoNoNo 
        
Tabular Models       
Programmability (AMO, ADOMD.Net, OLEDB, XML/A, ASSL, TMSL)YesYesYes*Yes*Yes*Yes*Third-party tool support, new XMLA endpoint for Power BI
HierarchiesYesYesYes**Yes*Yes*Yes**Simple hierarchies,
**AS supports HideMemberIfBlank
KPIsYesYesYesNoNoNo 
PerspectivesYes YesNoNoNo 
TranslationsYesYesYesNoNoNo 
DAX calculations, DAX queries, MDX queriesYesYesYesYesYesYes 
Row-level securityYesYesYesYesYesYes 
Multiple partitionsYes YesNoNoYes**Incremental refresh builds partitions
In-memory storage modeYesYesYesYesYesYes 
DirectQuery storage modeYes YesYesYesYes 
        
Multidimensional Models       
Semi-additive measuresYesNo 1Yes*Yes*Yes*Yes**Using DAX code,
Effort: moderate
HierarchiesYesYesYes**Yes*Yes*Yes**Simple hierarchies,
**AS supports HideMemberIfBlank
KPIsYesYesYesNoNoNo 
PerspectivesYes YesNoNoNo 
ActionsYesYes*Using 3rd party tool*Possible, limited*Possible, limited*Possible, limited 
Account intelligenceYesYesYes*Yes*Yes*Yes**Using DAX code,
Effort: high
Time intelligenceYesYesYesYesYesYes 
Custom rollupsYesYesYes*Yes*Yes*Yes**Using DAX code,
Effort: moderate
Writeback cubeYesYesNoNoNoNo 
Writeback dimensionsYes NoNoNoNo 
Writeback cellsYesYesNoNoNoNo 
DrillthroughYesYesYes*Yes*Yes*Yes**Multiple techniques
Advanced hierarchy types (parent-child and ragged hierarchies)YesYes*YesNoNoNo*Supports HideMemberIfBlank
Advanced dimensions (reference dimensions, many-to-many dimensions)YesYesYesYesYesYes 
Linked measures and dimensionsYesYes 2No*No*No*No**Equivelent functionality
TranslationsYesYesNoNoNoNo 
AggregationsYesYesYesYesYesYes 
Multiple partitionsYesYes, up to 3YesNoNoYes**Incremental refresh builds partitions
Proactive cachingYes *****In-memory model is always cached
Custom assemblies (stored procedures)YesYesNoNoNoNo 
MDX queries and scriptsYesYes*****Supports MDX queries & DAX scripts
DAX queriesYesYesYesYesYesYes 
Role-based security modelYesYesYesYesYesYes 
Dimension and cell-level securityYesYesNo*No*No*No**Equivelent functionality for measures
Scalable string storageYesYesYes*Yes*Yes*Yes**Equivelent functionality
MOLAP, ROLAP, and HOLAP storage modelsYesYesYes*Yes**cached or SSAS directYes**DirectQuery & hybrid models are equivelent or superior
Binary and compressed XML transportYesYesYes*Yes*Yes*Yes**VertiPaq in-memory compression on all data types
Push-mode processingYes YesYes*Yes*Yes**Multiple & different processing techniques supported
Direct writebackYes NoNoNoNo 
Measure expressionsYes YesYesYesYes 

Interviews with Microsoft Data Community Speakers and Leaders

What is the key to advancing your career in the Microsoft data platform?  Here is some advice from some of the most successful people in the industry…

Every year we have some big community events that bring together community leaders from all over.  These are international user group and community leaders who write books and speak at conferences. we had our local Oregon SQL Saturday “SQL Train” which is a chartered train – some coaches that one of our speaker’s chartered to bring all of our speakers and attendees up to the PASS summit after Oregon SQL Saturday, and then the big PASS summit (the big conference up in Seattle). I had a chance to sit down with a number of our speakers, community leaders and attendees and just ask questions about what brought them there, and advice that they would give people in the industry about how to get the most value out of that experience …and this is what they said:

Well-run monthly user group meetings and big annual events local events like SQL Saturday don’t just happen by themselves. It takes a lot of planning, a lot of volunteers and a lot of coordination to make these events successful. Part of that effort are the annual leadership planning meetings that we have during the week of PASS summit. Here are some short clips from those meetings where several hundred local leaders from all over the world got together to share ideas, to exchange notes and to coordinate to be able to make these events successful. Leaders cross-pollinate, exchange ideas and they work together to make this a great community. Why? …because somebody did that for us when we were getting started and we want to give back to the community. So, get involved; join the leadership committees at your local user groups, volunteer at SQL Saturday. Volunteer to do a short speaking engagement. Just get up and talk to some of your peers. Get started by volunteering in the community so that you can be part of the ongoing great community we have around the Microsoft data platform.

How to Assign Pro Licenses to a Power BI Tenant

This is a question that comes up all the time.  Power BI licensing is not complicated but a common challenge is that the person who sets up a new Power BI subscription and tenant within an organization is often not the same person who manages Office 365 or Azure service licensing for the organization.  I’ve consulted on projects for several organizations where folks just didn’t know who to talk to or how to proceed after testing the water with Power BI.  After setting up a new subscription, IT professionals and business data analysts often don’t know how to license Power BI for company use and share reports and datasets with others.

This post will show you how licenses are assigned to users and, more importantly, what to request from your support desk or administrators who may be unfamiliar with Power BI and Office 365 user licensing.  Keep reading for background information about why this is important and necessary.

You can use Power BI in one of three modes:

1) If you’re a one-person organization or don’t need to securely share content online with anyone, you can just use Power BI for free.  Yep, free.  No feature limits.

2) If your organization has a few hundred users or less, you will need to buy a Power BI Pro license for every user to author or view reports.  Unless you publish report content publicly to the web, every user must have a Pro license – period.

3) If you have several hundred users or you have so much data that you need to manage it with dedicated capacity, it may be cost-effective to purchase a Premium capacity tenant for a few thousand dollars a month.  Pro licenses are still required to publish reports but anyone in the organization can view published reports online or an on-premises Power BI Report Server.

Power BI Subscription Basics

Let’s say that I work for XYZ company and my work address is Paul@xyz.com.  Assuming that a Power BI subscription doesn’t yet exist, if I go to PowerBI.com and setup an account using my email address, I have created a Power BI subscription for my company that is a tenant within the Power BI service.  I could be the janitor for a multinational corporation but I am now the administrator of the tenant.

By way of definitions; the Power BI Service is the entire Power BI offering within the Microsoft Azure cloud.  At any time, it could encompass hundreds of virtual machines geolocated in data centers throughout the world.  When you subscribe to the service, you are – in effect – renting some space within the service.  The “space” that you rent and manage for your subscription is called a tenant.  It’s sort of like renting some office space or an apartment in a large building.  You don’t really own it but you are paying for the right to live there.  You can read about these and other administrative concepts here.

After setting up a new Power BI subscription, you really have one of two options:

1) If you have the authority to purchase licensing and manage services on behalf of your organization, proceed to purchase and assign licenses for report developers and users.

2) Make a service request or contact the appropriate administrator within your organization to add and assign licenses.  This might be your help desk, systems or operations admin or Office/cloud services administrator.

The Admin Take-Over

After a “less privileged” user sets up the first Power BI subscription for the organization, no one else can do the same.  This can be a little confusing if some person in Finance sets-up a trial account and then a BI developer tries to do the same thing.  If the organization plans to move-forward with a governed Power BI tenant, they can perform an “Admin Take-Over”.  Contrary to what the name suggests, this is not the same as a government coupe with troopers storming the building in Kevlar vests and automatic weapons.  It simply means that an administrator assumes control of the new tenant and transfers admin rights from the person who established the subscription the appropriate person(s).  Adam Saxton describes who this works in this Guy-In-A-Cube installment.

Web API Data Sources with Power Query and Scheduling Data Refresh in the Power BI Service

Using a Web API is a convenient way to expose and consume data over an Internet connection.  Exercising some essential design patterns, understanding and working with the Power Query Formula Firewall is essential if you need to consume Web API data with Power Query and schedule data refresh with the Power BI Service.

Having recently worked-through numerous issues with API data feeds and deployed report configurations, I’ve learned a few important best practices and caveats – at least for some common use cases.  In one example, we have a client who expose their software-as-a-service (SaaS) customer data through several web API endpoints.  Each SaaS customer has a unique security key which they can use with Power BI, Power Query or Excel and other tools to create reporting solutions.  If we need a list of available products, it is a simple matter to create a long URL string consisting of the web address for the  endpoint, security key and other parameters; an then just pass this to Power Query as a web data source.  However, it’s not quite that easy for non-trivial reporting scenarios. 

Thanks to Jamie Mikami from CSG Pro for helping me with the Azure function code for demonstrating this with demo data.  Thanks also to Chris Webb who has meticulously covered several facets of API data sources in great detail on his blog, making this process much easier.

Some web APIs have a database query or other logic hard-wired to each individual endpoint.  The endpoint I am demonstrating allows a stored procedure name and filter to be passed as parameters, which allows one endpoint to run any query in the database that is allowed by the developer or admin.  The following information was setup for this demo:

  • Here is the endpoint exposed through Azure Function App services:  https://intellibizwebapi.azurewebsites.net/api/ExecuteSQL
  • The security API key is required to authorize execution (*this key has since been retired):
    5FH6AcKwEDTChbOidYEpkveqlncgv/TOvqCAJ6jP2jX0fJUSmCVN0A==
  • The name of the stored procedure is passed in the “name” parameter like this: uspOnlineSalesByYear
  • An optional filter parameter may be passed to the stored procedure to filter the results.

The typical approach is to concatenating all the elements into a single string.  If we were to enter this address into a browser window:

https://intellibizwebapi.azurewebsites.net/api/ExecuteSQL?code=5FH6AcKwEDTChbOidYEpkveqlncgv/TOvqCAJ6jP2jX0fJUSmCVN0A==&name=uspOnlineSalesByYear&filter=2008

…a JSON document is returned beginning with the following data:

[{“SalesKey”:4,”DateKey”:”2008-01-13T00:00:00″,”channelKey”:2,”StoreKey”:306,”ProductKey”:1429,”PromotionKey”:10,”CurrencyKey”:1,”UnitCost”:132.9000,”UnitPrice”:289.0000,”SalesQuantity”:8,”ReturnQuantity”:0,”ReturnAmount”:0.0000,”DiscountQuantity”:1,”DiscountAmount”:57.8000,”TotalCost”:1063.2000,”SalesAmount”:2254.2000,”ETLLoadID”:1,”LoadDate”:”2010-01-01T00:00:00″,”UpdateDate”:”2010-01-01T00:00:00″},{“SalesKey”:5,

…and if we paste the same URL into the address dialog for a new web data source in Power Query, a complete table is returned.

image

Good so far, right?  But, here’s here’s the problem.  If we were to use a single API call in this manner, the Power BI service may, under certain conditions, allow the data source to be refreshed but you cannot modify the query string parameters in this way for the service to trust the web API connection so it can be refreshed.  Now for a more sophisticated and more realistic example.

To minimize the data volume per call and load data incrementally, web API data is often paged or filtered using a date range or category of some kind.  In this example, one call returns a list of years for which there are orders.  With that, orders can be loaded for each year.  An actual production scenario may be more sophisticated but this demonstrates the design pattern.

The first query – the outer query – returns one row per year.  Then we create another query that executes a stored procedure requiring a Year parameter, that returns all the Order records for that year. 

SNAGHTML1e42f4b4

That query, shown here, is converted into a function by adding the Year parameter.

image

In the typical design pattern, a custom column is added which invokes the custom function, passing the YEAR column value.

I deploy a copy of this report file to my Power BI tenant and then try to schedule data refresh.  Here’s the result:

image

The error reads: “You can’t schedule refresh for this dataset because the following data sources currently don’t support refresh…”

The Power Query Formula Firewall prevents the queries from running because they don’t meet requirements to be trusted according to the rules of the the formula firewall and the “fast combine” feature.  Each query must be granted permission to run.  The default permission set for web sources is “Anonymous” which simply means that no credentials are needed.  The developer simply needs to agree to let the query run.

Each query being combined must share a common address or connection string, with a compatible set of privacy level settings.

image

The formula firewall has a problem with us concatenating the endpoint, code and parameters into one humungous web address string.  The reasons and details are less important but separating the address and other elements and letting Power Query manage them as separate objects can resolve this issue.

Here’s the refactored M code for the outer query/function.  This is the patterned I’m using for all web API queries.  Note that the “BaseUri” is a scalar text type variable, and the other web query parameters are elements of a record stored in a variable named “Query”.  These two variables are passed as arguments to the Web.Contents method:

(Year as number) =>
let
     BaseUri     = “https://intellibizwebapi.azurewebsites.net/api/ExecuteSQL”,
     QueryRecord =
         [
             Query=
             [
                 code   = “abFbalkeuCiozdne7PeMG0bZWAZGj65uJ3zLsYoB8zLfisrJo6gv2/Fvw==”,
                 name   = “uspOnlineSalesByYear”,
                 filter = Number.ToText( Year )
             ]
         ],
     Source = Json.Document(
         Web.Contents( BaseUri, QueryRecord )
     ),

By letting the Web.Contents method work it’s magic and by conforming to the other requirements I mentioned, the Power BI service formula firewall will trust the source and allow this report to be scheduled for data refresh.

As I mentioned earlier, Chris Webb has covered a number of nuances related to this method on his blog.  To understand it deeply, I suggest reading about them here.

Mastering Power Query in a Day – Full-day training in Seattle

Following the Power BI World Tour, Seattle event on Oct 30, please join me for a full-day of deep learning.  That’s right… it’s on Oct 31st so put on your Wonder Woman or Captain America costume and get ready to exercise your super powers with Power Query and Power BI!  You will learn to master Power Query extensively from Beginner to Advanced.  The other session taught at the same time by Brian Grant is “Power BI: Enhance Your Data Model with DAX” but ya gotta pick one.  You can learn more about the Power BI World Tour and the Academy by following these events on Twitter and LinkedIn using the links at the bottom of this post, or search these hashtags:
#PowerBIUG | #PowerBI | @pbiusergroup | #PowerBIUGAcademy | #PBIWorldTour

Registration and course description:

Academy – Mastering Power Query in a Day (Seattle, WA)

The foundations of a Business Intelligence solution are data transformations, data wrangling, data cleansing and ETL. A well-crafted Power BI project rests on Power Query and the queries that define the data model, calculations and report visuals. This full-day session will teach you how to lay the foundation for a Power BI solution with simple and advanced Power Query techniques.

Learn from Paul Turley, ten-year Microsoft Data Platform MVP and veteran BI Solution Architect. You will learn best practice design patterns, tricks, shortcuts and proven techniques to improve your skills and add immediate value to your projects. Power Query is everywhere – and growing.

The skills and techniques taught in this workshop apply to Power BI Desktop, the “Get Data” feature in Excel 2016+, SQL Server Analysis Services 2017+ (SSAS), Azure Analysis Services (AAS) and Data Flows in the Power BI Common Data Service (CDS). You will learn through exercises and instructor-led hands-on demos. Bring your laptop with the latest version of Power BI Desktop installed. The rest will be provided. We will cover material from basics through advanced. Each exercise is separate so you can absorb only what you need to learn, based on your prior experience, needs and skill level.

Power Query Basics
Quick tour of the Power Query interface & essentials
Creating and managing queries
Adding and editing steps
Recovery and project management
Essential best practices
Managing data sources
Working with folder paths, web URIs & database connections
Referencing & Duplicating queries
Consolidating queries, building base queries & dependency chains
Loading queries into data model tables
Basic error handling & debugging

Data Sources & Structures
Flat CSV files
Irregular text files (headings & totals)
JSON (simple)
JSON (complex, with nested & ragged hierarchies)
SQL server
Excel (single sheet/table, multiple sheets/tables)
Folders & file collections
Web pages a page tables
Web APIs & web service endpoints

Essential Query Techniques
Managing data types
Applying correct naming conventions
Working with Date & Time values
Splitting & formatting columns
De-duplicating & grouping
Pivot, Unpivot & Transpose
Custom columns & expression basics
Extracting tables from a data sources to supporting essential modeling for Power BI report design:

  • Fact tables
  • Dimension tables
  • Bridge tables
  • Slicer & calculation-driver tables
  • Role-playing dimensions

Advanced Power Query Techniques
Working with M: The Data Mashup language
M function essentials
Prioritized learning (what’s most important)
Using & managing parameters
Using the #shared object for internal documentation, examples & code syntax
Understanding M objects (values, tables, lists & records)
Number, Date, Time & Text manipulation M functions
Create a Date lookup/dimension table using M & Power Query
Create a Time series lookup/dimension table using M & Power Query

  • Why do I need a Date dimension in Power BI?
  • Standard date parts & hierarchies
  • Columns to support time-intelligence calculations
  • Working with fiscal & special-purpose calendars (e.g. 4-4-5, ISO)
  • Working with query functions
  • Parameterized queries, API endpoints & user-defined functions

Putting it Together
Examples:

  • Queries to support data model construction
  • Queries used to support report visuals
  • Deploy a report, configure the on-premises gateway
  • Use query parameters to schedule refresh in a deployed Power BI solution

Follow the events at:

https://www.linkedin.com/groups/13580234

https://twitter.com/pbiusergroup?lang=en

How to Configure the Power BI Gateway to use Dataset Connection Parameters

A service provider or vendor might want to publish multiple copies of a report that should connect to different database servers or databases.  In a true multitenant service solution, we would have a singe database with row-level user mapping tables that filter data by the logged in user.  True multitenant solutions require quite a lot of planning and development work to implement.  In smaller-scale or interim solutions, copies of a report can be deployed to different workspaces and then queries can be parameterized to use different database connections.

In this post, I’ll demonstrate deploying and configuring such a solution where the server name and database name have been parameterized and setup to use the on-premises gateway to connect and refresh data.  I’ll also setup scheduled refresh.  The full video walk-through is below but I’ll do a quick review to set the stage.

This is the Power Query Editor in Power BI Desktop.  I have two parameters that are used to specify the ServerName and DatabaseName for each SQL Server query:

Power BI Gateway with Parameters (Time 0_00_57;24)

Once deployed to a workspace in the service, the gateway must be configured with a data source for every possible server name and database combination.  In my example, I can connect to my local server using the NetBIOS name, IP address or LocalHost.  These all are acceptable methods but a data source must be added to the gateway configuration for each so the that the connection strings match exactly.  Remember that the connection is from the on-prem gateway to the database server so names like LocalHost or an internal IP address will work just fine.  In my example, I’m using the IP address of my local loopback adaptor on the local machine to connect to a local instance of SQL Server over the TCP connection.

Power BI Gateway with Parameters (Time 0_04_31;15)

In the workspace, the dataset is bound to the gateway.  Click the ellipsis and choose Settings.

Power BI Gateway with Parameters (Time 0_02_44;22)

To bind the gateway to the dataset, click to select the radio button next to the gateway.  This flips the switch titled “Use a data gateway”.  Apply the setting and then you can refresh the data or schedule refresh.

Power BI Gateway with Parameters (Time 0_05_47;00)Power BI Gateway with Parameters (Time 0_06_09;27)Finally, the parameters can be updated right here in the dataset settings.

Power BI Gateway with Parameters (Time 0_06_46;21)

 

Video Demonstration

 

Pareto, Burn-down & Accumulating Trend Charts in Power BI

I’m managing an Agile team project using Microsoft Teams – the new project management platform integrated with Office 365.  Teams is a simple and useful project management tool but it’s new and light on features.  Using Power BI, we want to show the hourly task burn-down for each two-week sprint.  In JIRA and some other more mature project management platforms, the burn-down chart is a standard feature in the tool that shows the number of hours or story points remaining, compared to the estimated number for the sprint.  Just as I began working on that, a client asked for some help creating a Pareto chart and it occurred to me that burn-down and Pareto charts are very similar variations of the same type of chart presentation.  These are not so much chart types as they are a set of calculations and techniques for displaying a desired result.

Project Hours Burn-Down Chart

Here’s the Burn-down chart showing days of the sprint on the X-axis and the hours for all resources as columns.  The burn-down line represents the number of estimated hours remaining for the duration of the sprint as a percentage of the total estimated hours for all resources.

imageHere’s another variation of the burn-down chart using stacked columns for each project resource, in my example; developers named Marta, Rob and Vivek.  Again, the burn-down line shows the daily percentage of estimated hours remaining compared to the total hours that have been “burned” for all days up to and including the current day.

image

The columns in the chart are simply the sum of hours reported.  I didn’t even create a measure for this value.  It’s just a summable column in the ‘Project Hours’ table named “Hours”.  Here’s a look at the data, including the measures used in the chart:

image

…and finally, here is the DAX measure code used for the line part of the column/line combination chart:

Project Hours Remaining % =
VAR DayAccumHours =
CALCULATE( SUM( ‘Project Hours'[Hours] ),
FILTER( ALLSELECTED( ‘Day’ ),
‘Day'[Day Number] <= MIN( ‘Day'[Day Number] )
)
)
VAR ProjectHoursRemaining = SUM( ‘Sprint Estimates'[Est Hours] ) – DayAccumHours

RETURN
DIVIDE( ProjectHoursRemaining, SUM( ‘Sprint Estimates'[Est Hours] ) )

Pareto Chart

This style of chart is very similar but the key differences are that the columns are ordered by the contribution value in descending order.   You can see that my sample dataset just uses numbers for the categories (e.g. “8”, “2”, “4”, etc.) but these could just as easily be names of resources, sales people, customers or products.  The columns are in descending order of aggregate value rather a time series of the axis field value.  To order by measure or value (my aggregate field is literally called “Value”), set the Type property for the X-axis to “Categorical”.

SNAGHTMLc49dd9d

Here’s the Pareto chart.  The contribution line shows the percentage of the total contribution that a category item and all of it’s predecessors in ranked descending order have made to that point.  For example, the first four top-ranking categories (8, 2, 4 and 5) account for about 50% of the total.

image

The DAX measure code for the accumulating percentage value used for the line, is as follows:

Accum Value % All =
VAR AccumValue =
CALCULATE( SUM(‘Pareto Series'[Value] ),
FILTER( ALLSELECTED( ‘Pareto Series’ ), ‘Pareto Series'[Value] >= MIN( ‘Pareto Series'[Value] ) )
)
VAR AllSeriesTotal = CALCULATE( SUM( ‘Pareto Series'[Value] ), ALLSELECTED( ‘Pareto Series’ ) )

RETURN
DIVIDE( AccumValue, AllSeriesTotal )

Accumulating Trend Series or Trend Chart

For good measure, I have one more example.  I actually started experimenting with calculation techniques using this chart.  It is essentially the burn-down chart example with the line presenting the opposite of the burn-down, so maybe this is a “burn-up chart” – I don’t know.  This may be useful when you want to visualize a time or other continuous series on the X-axis and the accumulating contribution as a line along with the actual contribution values as columns.

image

…and the measure DAX code used for the trend line:

Accum Series Value % All =
VAR AccumSeriesValue =
CALCULATE( SUM(‘Pareto Series'[Value] ),
FILTER( ALLSELECTED( ‘Pareto Series'[Period] ), ‘Pareto Series'[Period] <= MIN( ‘Pareto Series'[Period] ) )
)
VAR AllSeriesTotal = CALCULATE( SUM(‘Pareto Series'[Value] ), ALLSELECTED(‘Pareto Series’ ) )

RETURN
DIVIDE( AccumSeriesValue, AllSeriesTotal )

I’m hopeful that this will be useful and save you some time and energy.  As always, please leave comments and reach-out if you need some help.

You can download the example Power BI Desktop file here: Simple Pareto & Burn-down Chart Example.zip