Is it possible to create variables in Power BI similar to summarization results from an Excel Pivot?

[Anonymous]

Hi All,

I am new to Power BI and I am working on a project where I am supposed to replicate the excel charts to Power BI. These excel charts are designed using summarization measures from different pivot tables. Our goal is to load raw excel file into Power BI and automate as much as possible so the visualizations are updated every time excel is updated. However, I am having hard time on how to recreate these charts built using summarization from excel pivots.

For eg, see below for one of the pivots used to build a chart

It has 5 different filters on Ref, Type, Subtype, Role and dept. The data is displayed for Division and Division Name. Creating a new column using CALCULATE & SUM is giving a aggregated count irrespective of division and division name. So, I am stuck on how to create "Total Budget" measure efficiently so I can filter it by division and also use it in a line chart with other measures (like in image below)

Edit:  Another issue I have is that the pivot chart I have in excel is using measures from multiple pivots. For eg, take the image below as an excel chart, it has 3 measures  - Budget, Design Costs, Construction costs. These 3 measures came from 3 different pivots by summarizing different amounts. My goal is to create variables which are summarized results of these pivots. Is there a way to create calculated measures which give similar summarization results like a pivot table in excel?

Image: https://www.clearpointstrategy.com/dashboards/budget-vs-actual-dashboard/

Any help is appreciated. Thanks a lot!

[v-yalanwu-msft]

Hi, @Anonymous ;

You could use Line and stacked column chart , then date as x-axis, then add values or column series.as follows:

But don't know what the structure of your data is? Can you share a simple data after removing sensitive information, as well as screenshots of the results you want to output in Excel? So that we can restore better at Power BI.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi,

Thanks for the response. My issue is not in building the visualization but is more about structuring the data and creating the variables that replicate the pivot tables built in excel. At this time, I have learnt to pivot the columns in Power BI to match the pivots in excel using the "Pivot Column" option in Power Query. 

[amitchandak]

@Anonymous , You need to use Matrix visual

row - Division and Division Name

Column - Year

Measure on Values - Sum of a column

[Anonymous]

Hi Amit,

Thanks for your quick response. I have tried doing that way. However, one issue I have is that the pivot chart in excel is using Sum(column) from 3 different pivot charts. 

For eg, one pivot is showing budget, one pivot is showing expenses. A chart is designed in excel showing Budget, expenses, budget left in the same chart. How can I replicate that if I design a matrix with one pivot?

[amitchandak]

@Anonymous ,not able to visualize what you want.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Thanks again for quick response. I found a google image that looks similar to my excel chart.

Image courtesy: https://www.clearpointstrategy.com/dashboards/budget-vs-actual-dashboard/

If you see in the chart above, it has 3 measures -  Budget, design costs, construction costs. If those 3 measures are results from 3 pivots, how would you be able to replicate it in Power BI? The image I shared originally is showing one pivot that gives the total budget count and budget by division.