Category Archives: Self-Service BI
Calculation Groups to the Rescue!
To set the stage, I need you to travel back in time with me a bit.
The year was 2019, and none of us had ever heard of COVID-19…those were simpler times.
I was working on a data model that had A LOT of dates in the data table. By a lot, I mean 10 different dates in a single transaction table. I was trying to build a model that would be able to answer any question that the users would ever ask, so I ended up including all 10 different dates. The problem came when I started building out my relationships between my data table and my calendar table. Power BI would only let me have 1 active relationship between the data table and the calendar table…but I had 10 dates that I wanted to relate to the calendar table.
As all good developers do, I went to the inter-webs for help.i (In the back of my mind, I knew the answers I was going to find, but I was hoping to find some magical solution.) Sure enough, the web returned the two options that I was hoping to avoid: either create (9) copies of my calendar table to match the number of dates I had in my data table (gross), or create 9 inactive relationships between my calendar table and activate those relationships via DAX (less gross, but still not ideal). Let’s say I had 5 base measures in that model. I would have to create 9 copies of each of those measures, activating each of the inactive relationships to the calendar table. My 5 measures quickly would have become 50 different measures. That felt too bulky and confusing for report authors.
I didn’t like either of my options, so I took a stab in the dark and started emailing Power BI and data modeling experts asking for advice. One was gracious enough to respond, confirming the two options that I already didn’t like, but he did offer a third solution:
Calculation Groups
I read what I could find about Calculation Groups, and they sounded incredible! Unfortunately for me, they were not available in Power BI, and I didn’t have access to Azure Analysis Services or Analysis Services 2019. Calculation Groups seemed like a magical unicorn that was too good to be true, especially because they seemed to solve my exact problem, but I couldn’t use them.
Let’s jump back in our time machine and return to the present-day. Unfortunately, we’ve all now heard of COVID-19, but it isn’t the only thing that popped up in 2020. Calculation Groups are now out in the wild and available in Power BI, with the help of Tabular Editor!
I have since moved on from that 2019 model with 10 dates, but I have a new model that has similar issues that Calculation Groups can now solve!
Let’s take a look at my model:
It is a fairly simple model, and I only have 5 base measures to start with:
If you look closely at the model view, you’ll see that there is an inactive relationship between Date and Invoices, because my Invoices table has Invoice Date and Delivery Date. Well, now I need to create a copy of my 5 base measures (CALCULATE…USERELATIONSHIP), so that report authors can look at each of them by either Invoice Date or Delivery Date.
Ok, 10 measures isn’t that bad…I can manage that. But…I also want to do some time intelligence analysis, things like prior year, this year – last year, % growth, MTD, QTD, YTD. I feel like I’m back in 2019 again because my 5 base measures turned into 10, and then with the addition of time intelligence, I’m at 70 measures!
Unlike in 2019 though…Calculation Groups actually do come to the rescue!!!
With the help of Calculation Groups, I am going to keep my measure count to the 5 base measures that I started with, but I am going to add a Calculation Group to handle my time intelligence and a Calculation Group to handle my inactive Delivery Date relationship.
I start by launching Tabular Editor from the External Tools ribbon in Power BI Desktop.
Once Tabular Editor opens, I right click on Tables and create a new Calculation Group, which I name Time Intelligence.
I also rename the column from Name to Time Period.
Next, I right click on Calculation Items
and create the following items.
Below is the DAX expression for each of those Calculation Items:
| Calculation Item | DAX Expression |
| CY | SELECTEDMEASURE() |
| PY | CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( ‘Date'[Date] ) ) |
| vs PY | VAR _CY = SELECTEDMEASURE () VAR _PY = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( ‘Date'[Date] ) ) VAR Result = _CY – _PY RETURN Result |
| Vs PY% | VAR _CY = SELECTEDMEASURE () VAR _PY = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( ‘Date'[Date] ) ) VAR Result = DIVIDE ( _CY – _PY, _PY ) RETURN Result |
| MTD | CALCULATE ( SELECTEDMEASURE (), DATESMTD ( ‘Date'[Date] ) ) |
| QTD | CALCULATE ( SELECTEDMEASURE (), DATESQTD ( ‘Date'[Date] ) ) |
| YTD | CALCULATE ( SELECTEDMEASURE (), DATESYTD ( ‘Date'[Date] ) ) |
(The SELECTEDMEASURE() in each of the Calculation Items is kind of like a placeholder. It means that the logic of the Calculation Item will end up being applied to whatever measure I end up visualizing in the report.)
That’s a great start!
Now I can save my changes in Tabular Editor and refresh those changes in Power BI Desktop.
Now that I have my Time Intelligence Calculation Group/Items in Power BI Desktop, I can use the Time Period column in a slicer and/or as a column in a matrix. (I’m putting Sales as the measure in the values of the matrix. Because the Calculation Items have that SELECTEDMEASURE() placeholder, they will be applied to Sales. If I chose to use Quantity instead of Sales, all the time intelligence logic would be applied to Quantity…cool!)
I don’t care who you are, that’s freakin’ awesome!
But…let’s take it a step further. Remember when I mentioned my inactive relationship based on Delivery Date? I can create a Calculation Group to toggle between the active relationship and the inactive relationship.ii
Same steps as before:
Create new Calculation Group, rename column, add Calculation Items:
| Calculation Item | DAX Expression |
| Invoice Date | SELECTEDMEASURE() |
| Delivery Date | CALCULATE ( SELECTEDMEASURE (), USERELATIONSHIP ( ‘Invoices'[Delivery Date Key], ‘Date'[Date] ) ) |
(Remember that concept of the SELECTEDMEASURE() placeholder? The Invoice Date Calculation Item isn’t really doing anything, since the relationship based on Invoice Date is the Active relationship. It’s the Delivery Date Calculation Item that is doing something special, in that it is activating the inactive relationship, for the measure(s) in the visual.)
After saving in Tabular Editor and refreshing in Power BI Desktop, I can now add that Date to Use column as a slicer on my report, to toggle between Invoice Date or Delivery Date…and it can work along with my time intelligence Calculation Group!!!
Awesome!
I want to add one last bit of icing on this cake, because I want users to be able to pick which measure(s) they see in the visual. For example, everything up to this point has been looking at Sales, but what if a user wanted to see both Sales and Margin%, but another user just wants to focus on Quantity?
Calculation Groups to the rescue!
Just like before, I open Tabular Editor and create a new Calculation Group, rename column, and add Calculation Items. This time though, I am just going to hard code each of my 5 base measures as Calculation Items.
| Calculation Item | DAX Expression |
| Quantity | [Quantity] |
| Sales | [Sales] |
| Cost | [Cost] |
| Margin | [Margin] |
| Margin% | [Margin %] |
Now with my Choose Measure column from the Measure Picker Calculation Group as a column in my matrix and a slicer on my report, I have a fully dynamic visual, that allows users to pick which measure(s) to see, along with which date relationship, and which time intelligence logic!
i Is a blog post really a blog post without at least one footnote or endnote?
ii When using multiple Calculation Groups together, it is extremely important to pay attention to the Calculation Group Precedence property in Tabular Editor, to ensure that the different Calculation Groups are executed in the correct order. For further reading, see these super helpful posts by SQLBI.
Introducing Calculation Groups – SQLBI
Understanding Calculation Group Precedence – SQLBI
iii One quick note about performance. The USERELATIONSHIP function in DAX disables some optimizations in the Power BI engine, and Time Intelligence calculations like MTD, QTD, and YTD can be non-additive and can create quite a few Storage Engine queries. This combination of Calculation Groups could result in slower performance in a large or complex model.
We’re Changing Our Name (and Nothing Else!)
An overdue change for which there was never a good time
In the Fall of 2009, I created PowerPivotPro.com in order to begin blogging about this new technology called Power Pivot. I’d seen what it could do, and knew it was going to change the world. People needed to know about it! So, the blog was born.
Separately I knew that sooner or later, I was going to launch a business around this new tech, but hey, that would be a separate site, and PowerPivotPro seemed like a great “call sign” for a blogger, so… “don’t worry about it,” I told myself. “Business URL comes later!”
Heh. Famous last words. Here we were, twelve years later, with a name that we outgrew long ago. Good problem to have, really, but we’ve known for many years now that we needed to make a change.
WHEN to make the change turned out to be a difficult thing however. An unfun thing – LOTS of work, with no immediate benefit. So we kept putting it off in favor of more impactful projects.
Well, a year ago we took our first step – making P3 our primary logo, de-emphasizing PowerPivotPro, and setting a one-year timer for changing the URL and retiring “PowerPivotPro” to the history books. The time has come.
..even MORE important to us was that it reflect our CLIENTS. The people who, like us, understand that growth and durability both require an openness to change – and the capability to identify the RIGHT changes. The capability to then EXECUTE, and to do so at a modern tempo.
Why “P3 Adaptive?”
We’ve long been known informally as P3, and heck, we even call OURSELVES that internally, so for continuity’s sake – the P3 was always going to stay.
For the other “half” of the name, we wanted it to reflect our own internal culture, of course, but even MORE important to us was that it reflect our CLIENTS. The people who, like us, understand that growth and durability both require an openness to change – AND the capability to identify the RIGHT changes. The capability to then EXECUTE, and to do so at a modern tempo.
This has been our own internal journey at P3, as well as the journey we’re on with our clients. Even before COVID reminded us so starkly, it was always true: to be Adaptive is to be Successful. And “P3 Successful” sounded a bit silly 
A big change that’s also kinda nothing
Emotionally, this is a big moment for me, but for our clients and the community, it’s not a big deal at all. Nothing changes. Our old email addresses still work (as well as the new ones). ALL of the PowerPivotPro.com URL’s SHOULD redirect to their equivalent on P3Adatptive.com. There aren’t any mandatory accounting changes. It’s actually been SUPER smooth, particularly with the O365 stuff. Microsoft, we are impressed!
“C’mon, are you SURE you’re not hiding a bigger change?”
Nothing up our sleeves here. We aren’t being acquired, we’re not bringing in new executives, we’re not re-organizing, and nobody is leaving. Just setting the table for our continued growth, which has been explosive of late.
(Speaking of which, lately we’ve been hiring an average of one consultant each week! Have you considered applying?)
Planning at the speed of COVID: P3 Adaptive and Power On
It’s not adversity which defines you, but how you respond to it.
GPS and Steering Wheel
Power BI = GPS. Power On = Steering Wheel.
In 2020 we’ve been talking a lot about how important it is to be both intelligent AND agile in the face of unpredictable business conditions. During “normal” times, you can often get away with guessing that tomorrow will resemble yesterday, and simply following your well-worn business map. But 2020 has thrown all of that out the door, and even though 2021 is hopefully kinder, we all know that it will be far from a return to normal.
To survive and thrive in chaos, you have to SEE changing conditions clearly and quickly, but then also ACT – decisively. And taking action is a team sport – everyone needs to know their new targets. They need to believe in them. And they need to know how their actions play into the new overall plan.
Agile Planning, Integrated with Power BI
Register now for our webinar on agile planning
The velocity at which you need to change plans is unheard of now and as such, planning needs to be nimble and continuous. To facilitate this we need tools that enable faster and more collaborative planning. We’ve been using PowerOn for several years now with our clients and it has performed very effectively in this role, resulting in a more effective and efficient planning process as well as organizational gains.
Organizational planning is a holistic exercise. Changes to forecasts and assumptions can have sweeping impacts across the organization. With our solution utilizing Power BI and PowerOn visual planner we can instantly see how changes to our input assumptions and forecast, impacts everything from financials including the P&L, Balance Sheet, and Cash Flow, to operational functions including inventory, production, headcount, resource load, and training needs.
Planning also cannot exist in a vacuum. We know that as we seek to make planning more agile and continuous we need to make it more collaborative. Of course, in the past, the more collaborative the process, the slower it would be as there would be more versions of the forecast and plans to keep track of and update over time as the actuals changed.
PowerOn’s Visual Planner solution includes a workflow process to review and approve forecasts and add commentary/notes on any data point. This. facilitates a collaborative process that includes input across the organization while not bringing forward movement to a crawl.
The ability to continuously plan and make decisions in an agile, collaborative way is not just a competitive advantage anymore, it is essential to future success.
Want to learn more about how PowerBI and PowerOn can help transform your planning? Please join us on Thursday, December 10th from 12 pm – 1 pm CST for a webinar.
P3 Adaptive Jobs: Time to Come Home?
Welcome Home: It’s not just a bombastic prog-metal song
Continuous hiring mode
In P3’s history, we’ve had a bit of a “reactive cyclical” hiring model. Demand for our services would grow, stretching us to the limits of staffing, so we’d add 1-2 new consultants. And those new consultants would instantly take us from “short of capacity” to “excess capacity,” so we’d kinda pause hiring for awhile. Then the cycle would repeat.
I think that era is over, and we’re now going to be continually hiring. The value proposition we bring to clients is second to none, and that reputation has a way of getting around over time. Plus we now have a full-time marketing department (I have a team reporting to me again!), and they’re very good at what they do.
The Best Place to Work
If you’re keen on the Power Platform, I think we are THE best place to work. That’s the kind of bold claim that must be followed up with some “why” detail, so here goes…
We are committed to The Right Thing.
You know all those frustrating moments when you KNOW something could be faster/more effective/less stupid, but “the powers that be” block the proposed improvement for some sort of selfish or fearful bureaucratic reason? Or even worse, because they don’t like where the idea came from (aka you)? Yeah, that doesn’t happen here. Our clients hire us, by definition, because they want to evolve. If they wanted slow, wasteful, and bureaucratic, they’d hire one of the more traditional firms in this space.
For our clients, we always pursue the path that provides the maximum possible benefit in the shortest responsible timelines. We don’t milk projects or drag them out. If something can be done for a client in a day, we do it in a day, and we’re always raising the bar in terms of how we can help their business. We feel GOOD about what we do, and if we don’t, we speak up and make changes.
Some people might call this an overrated benefit, but I chose to lead with it, because it’s HUGE. There’s something about doing valuable work, and being appreciated for it – for seeing its impact, and hearing people GUSH at you about how much you’ve helped them – that makes work feel a lot less like work. As human beings we are WIRED for this sort of positive, small-scale cooperation, and at P3, our business model is to lean into that – hard.
Corollary: it’s rarely boring.
We also don’t hoard knowledge from our clients, so over time, some of them decide to take on more and more of the work themselves, and lean on us for the harder parts. On average, this means that the projects we execute tend to be more “intellectual challenge” than “grinding slog.”
And since we avoid project plans that waste clients’ time (above), we find ourselves in the stimulating end of the pool more often than the boring one. Yes, ALL jobs have some boring parts, but we’re lighter on those than you’ll find anywhere else.
The pay is competitive. Then we add 30%.
We pay a good salary and offer top-notch benefits, so that’s good. But then we do something unusual: on average, our consultants make an additional 30% (relative to their salary) in monthly incentive bonuses.
We have a “when the company wins, you win” philosophy, and we back that with money – significant money. Attaboys (and Attagirls!) are nice, but we prefer to accompany them with currency rather than Starbucks gift cards, because that’s what WE would prefer.
We’ll still be a remote company when COVID is gone.
We shrugged off COVID because we
were ready for it before it happened
Long before COVID forced other companies into “work from home mode,” we were already 100% operating on that model. We know how to make it work, we prefer it, and we have no plans to convert to office-based when the pandemic passes.
So, no commute ever again – reclaim those hours of your life forever. And as long as you’re hitting your deliverables, the job is super flexible about how you juggle your day between personal and professional demands.
In fact we’re such an outlier on this remote work front (and our incentive plan) that Authority Magazine interviewed me about our approach last year.
You = “our people.”
Future team members who are reading this, you are ALREADY one of us, you just haven’t showed up yet 
Our interview process doesn’t explicitly select for this sort of thing, but it might as well, because everyone who comes through it shares some amazing qualities – human qualities. The people at P3 Adaptive are funny, warm, interesting, and helpful. Talented and self-deprecating. Underappreciated and often isolated in their former roles.
But here, it’s kinda like that tap-dancing “bee girl” in the Blind Melon video when she finds her Bee People at the end:
Such a cheesy metaphor that I wouldn’t dare say it if it weren’t true
It’s kinda bizarre, considering the probabilities involved at our size, but we don’t have any jerks! ZERO! Just really cool people. I don’t understand it, but I love it.
And I’m looking forward to seeing you around our virtual hallways
P3 Jobs: Time to Come Home?
Welcome Home: It’s not just a bombastic prog-metal song
Continuous hiring mode
In P3’s history, we’ve had a bit of a “reactive cyclical” hiring model. Demand for our services would grow, stretching us to the limits of staffing, so we’d add 1-2 new consultants. And those new consultants would instantly take us from “short of capacity” to “excess capacity,” so we’d kinda pause hiring for awhile. Then the cycle would repeat.
I think that era is over, and we’re now going to be continually hiring. The value proposition we bring to clients is second to none, and that reputation has a way of getting around over time. Plus we now have a full-time marketing department (I have a team reporting to me again!), and they’re very good at what they do.
The Best Place to Work
If you’re keen on the Power Platform, I think we are THE best place to work. That’s the kind of bold claim that must be followed up with some “why” detail, so here goes…
We are committed to The Right Thing.
You know all those frustrating moments when you KNOW something could be faster/more effective/less stupid, but “the powers that be” block the proposed improvement for some sort of selfish or fearful bureaucratic reason? Or even worse, because they don’t like where the idea came from (aka you)? Yeah, that doesn’t happen here. Our clients hire us, by definition, because they want to evolve. If they wanted slow, wasteful, and bureaucratic, they’d hire one of the more traditional firms in this space.
For our clients, we always pursue the path that provides the maximum possible benefit in the shortest responsible timelines. We don’t milk projects or drag them out. If something can be done for a client in a day, we do it in a day, and we’re always raising the bar in terms of how we can help their business. We feel GOOD about what we do, and if we don’t, we speak up and make changes.
Some people might call this an overrated benefit, but I chose to lead with it, because it’s HUGE. There’s something about doing valuable work, and being appreciated for it – for seeing its impact, and hearing people GUSH at you about how much you’ve helped them – that makes work feel a lot less like work. As human beings we are WIRED for this sort of positive, small-scale cooperation, and at P3, our business model is to lean into that – hard.
Corollary: it’s rarely boring.
We also don’t hoard knowledge from our clients, so over time, some of them decide to take on more and more of the work themselves, and lean on us for the harder parts. On average, this means that the projects we execute tend to be more “intellectual challenge” than “grinding slog.”
And since we avoid project plans that waste clients’ time (above), we find ourselves in the stimulating end of the pool more often than the boring one. Yes, ALL jobs have some boring parts, but we’re lighter on those than you’ll find anywhere else.
The pay is competitive. Then we add 30%.
We pay a good salary and offer top-notch benefits, so that’s good. But then we do something unusual: on average, our consultants make an additional 30% (relative to their salary) in monthly incentive bonuses.
We have a “when the company wins, you win” philosophy, and we back that with money – significant money. Attaboys (and Attagirls!) are nice, but we prefer to accompany them with currency rather than Starbucks gift cards, because that’s what WE would prefer.
We’ll still be a remote company when COVID is gone.
We shrugged off COVID because we
were ready for it before it happened
Long before COVID forced other companies into “work from home mode,” we were already 100% operating on that model. We know how to make it work, we prefer it, and we have no plans to convert to office-based when the pandemic passes.
So, no commute ever again – reclaim those hours of your life forever. And as long as you’re hitting your deliverables, the job is super flexible about how you juggle your day between personal and professional demands.
In fact we’re such an outlier on this remote work front (and our incentive plan) that Authority Magazine interviewed me about our approach last year.
You = “our people.”
Future team members who are reading this, you are ALREADY one of us, you just haven’t showed up yet 
Our interview process doesn’t explicitly select for this sort of thing, but it might as well, because everyone who comes through it shares some amazing qualities – human qualities. The people at P3 are funny, warm, interesting, and helpful. Talented and self-deprecating. Underappreciated and often isolated in their former roles.
But here, it’s kinda like that tap-dancing “bee girl” in the Blind Melon video when she finds her Bee People at the end:
Such a cheesy metaphor that I wouldn’t dare say it if it weren’t true
It’s kinda bizarre, considering the probabilities involved at our size, but we don’t have any jerks! ZERO! Just really cool people. I don’t understand it, but I love it.
And I’m looking forward to seeing you around our virtual hallways
#PowerBI – Change the data source in your composite model with direct query to AS/ Power BI Dataset
I have been playing around with the new awesome (preview) feature in the December Power BI Desktop release where we can use DirectQuery for Power BI datasets and Azure Analysis services (link to blogpost)
In my case I combined data from a Power BI dataset, Azure Analysis Services, and a local Excel sheet. The DirectQuery sources was in a test environment.
I then wanted to try this on the actual production datasets and wanted to change the datasources – and was a bit lost on how to do that but luckily found a way that I want to share with you.
Change the source
First you click on Data source settings under Transform data
This will open the dialog for Data source settings and show you the list of Data sources in the current file.
Now you can either right click the data source you want to change
Or click the button “Change Source…”
Depending on your data source different dialogs will appear
This one for my Azure Analysis Services Connection
And this one for Power BI Dataset
And this one for the Local Excel workbook
Hope this can help you to.
Happy new year to you all.
You must know about this shortcut key in #PowerBI Desktop
Working with the field list on a large model in Power BI Desktop can quickly make you end up with a lot of expanded tables and you collapsing them one by one.
Don’t do that
Even though that is good if you want to improve your chances of beating your kids in Fortnite – it probably won’t – so instead do one of the following
If you want to use your mouse
Click the show/hide pane in the header of the Fields panel
This will collapse all expanded tables in the field list at once – plus if you have used the search field – it will clear that as well.
But you want to do it using the keyboard use
ALT + SHIFT + 1
This will collapse all the expanded tables as well.
Here is a link to the documentation about short cut keys in Power BI desktop – run through them – there might be some that can save you a click or two
Keyboard shortcuts in Power BI Desktop – Power BI | Microsoft Docs
Planning at the speed of COVID: P3 and Power On
It’s not adversity which defines you, but how you respond to it.
GPS and Steering Wheel
Power BI = GPS. Power On = Steering Wheel.
In 2020 we’ve been talking a lot about how important it is to be both intelligent AND agile in the face of unpredictable business conditions. During “normal” times, you can often get away with guessing that tomorrow will resemble yesterday, and simply following your well-worn business map. But 2020 has thrown all of that out the door, and even though 2021 is hopefully kinder, we all know that it will be far from a return to normal.
To survive and thrive in chaos, you have to SEE changing conditions clearly and quickly, but then also ACT – decisively. And taking action is a team sport – everyone needs to know their new targets. They need to believe in them. And they need to know how their actions play into the new overall plan.
Agile Planning, Integrated with Power BI
Register now for our webinar on agile planning
The velocity at which you need to change plans is unheard of now and as such, planning needs to be nimble and continuous. To facilitate this we need tools that enable faster and more collaborative planning. We’ve been using PowerOn for several years now with our clients and it has performed very effectively in this role, resulting in a more effective and efficient planning process as well as organizational gains.
Organizational planning is a holistic exercise. Changes to forecasts and assumptions can have sweeping impacts across the organization. With our solution utilizing Power BI and PowerOn visual planner we can instantly see how changes to our input assumptions and forecast, impacts everything from financials including the P&L, Balance Sheet, and Cash Flow, to operational functions including inventory, production, headcount, resource load, and training needs.
Planning also cannot exist in a vacuum. We know that as we seek to make planning more agile and continuous we need to make it more collaborative. Of course, in the past, the more collaborative the process, the slower it would be as there would be more versions of the forecast and plans to keep track of and update over time as the actuals changed.
PowerOn’s Visual Planner solution includes a workflow process to review and approve forecasts and add commentary/notes on any data point. This. facilitates a collaborative process that includes input across the organization while not bringing forward movement to a crawl.
The ability to continuously plan and make decisions in an agile, collaborative way is not just a competitive advantage anymore, it is essential to future success.
Want to learn more about how PowerBI and PowerOn can help transform your planning? Please join us on Thursday, December 10th from 12 pm – 1 pm CST for a webinar.
Use hidden measures and members from #PowerBI dataset in an Excel Pivot table
When you connect to a Power BI Dataset from Power BI desktop you might have noticed that you can see and use hidden measures and columns in the dataset.
But the hidden fields cannot be seen if you browse the dataset in Excel.
But that does not mean that you cannot use the fields in Excel – and here is how you can do it.
Using VBA
You can use VBA by creating a macro
The code will add the field AddressLine1 from the DImReseller dimension as a Rowfield if the active cell contains a pivotable.
Sub AddField()
Dim pv As PivotTable
Set pv = ActiveCell.PivotTable
pv.CubeFields("[DimReseller].[AddressLine1]").Orientation = xlRowField
End Sub
If you want to add a measure/value to the pivotable you need to set change the Orientation property to xlDataFields
This means that we now have added two hidden fields from the dataset
Add hidden measures using OLAP Tools
You can also add hidden measures using the OLAP Tools and MDX Calculated Measure
Simply create a new calculated measure by referencing the hidden measure in the MDX
This will add a calculated Measure to the measure group you selected
And you can add that to your pivotable
Referencing hidden items using CUBE functions
Notice that you can also reference the hidden measures using CUBE functions
Simply specify the name of the measure as the member expression in this case as “[Measures].[Sales Profit]”
You can also refer to members from hidden fields using the CUBEMEMBER functions
Hope this can help you too.
Power On!
Free Workshop From Microsoft and P3: Modern Excel Analyst In A Day
A FREE Course By P3, In Partnership With Microsoft
on Tuesday December 8th, 2020 9am-5pm EST
Do you have Excel Analysts spending hours wrangling data week after week to provide updated reporting and analytics? Do you have Analysts starting to explore how Power BI can help but they’re in need of instructor-led training & hands-on workshop to gain experience? Are you unsure how Power Pivot, Power Query, Power BI, and Power Platform are all used together with O365, MS Teams, and SharePoint? If this sounds familiar, our team can help!
First come, first scheduled in this FREE event, and seating is limited to 50! Experience how Excel, together with Power BI, can empower your analysts to transform repetitive tasks into fully automated, action-driven solutions. Modernize and empower your organization while driving critical business impact. Did we mention that it’s a free workshop?
This workshop is perfect for Excel analysts, Power BI analysts, and data enthusiasts!
Click HERE to reserve your spot
On completion, attendees will:
- Have Awareness of Power Query & Power Pivot In Excel
- Learn how Power Query & Power Pivot In Excel can improve efficiency, provide reusable seamless solutions and transform existing manual reporting into actionable insights
- Understand how Excel maps into Power BI Desktop
- Identify opportunities to use Excel & Power BI together
- Integrate Excel & Power BI solutions into Microsoft Teams & SharePoint