cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gggar
Helper I
Helper I

Create summary table based on the final numbers in chart

Hello,

 

I want to ask, is it possible to create a summary table based on what's being displayed currently in my chart?

This chart involves complex calculation from multiple fact tables. It takes some time to load the final numbers whenever the user opens the report.

 

I wanna know if it's possible to dump the final result to a summarised form (maybe a PBI dataset?) so that the chart will load instantly whenever the report is opened? Without the need to go with the overall process of complex calculation.

 

For example, after complex calculation, the final form of my chart is sth like this:

gggar_0-1620499456165.png

So the summary table ideally stores 5 records, for each month. The summary table needs to be refreshed daily, to get the most updated numbers everyday.

 

Anyone knows the answer? Any help will be greatly appreciated! Thank you.

1 ACCEPTED SOLUTION
Fowmy
Super User IV
Super User IV

@gggar 

 

Yes you can use Dataflows to do the transformations but keep only the small tables over there. If your fact tables a huge, it's not that recommend to have in Dataflow, Power Query desktop is better in this case. Any how you test it in dataflows for facttables and move them if it's not performing well. 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn   Join my Power BI User

View solution in original post

5 REPLIES 5
gggar
Helper I
Helper I

Hi @Fowmy ,

 

Thanks for the suggestion.

I have been using this calculated table method in my report.

Is it possible to have all the process of generating this calculated table, done outside my PBI file?

So that PBI will not work on any processing, it will just retrieve the final result from the whole transformation done in the calculated table somewhere else.

 

Thank you.

Fowmy
Super User IV
Super User IV

@gggar 

 

It depends on your source data. If you want to push all the transformations and the summaries to the source then you will have to request the data source owner to do it for you, for example SQL DB administrator. they can create views based on your requirements. 

it's also possible for you to do these works in Power Query before loading the required data and summaries to your POWER BI data model. 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn   Join my Power BI User

Hi @Fowmy ,

 

Thanks for the suggestion. I might need to do the facts transformation to a summary one, instead of importing the facts themselves to PBI.

I have multiple data sources from multiple platforms.

Do you recommend using PBI Dataflow for all the transformation? I have complex JOIN and DAX within the transformation.

Would it be possible to generate this final result as a summary table in PBI Dataflow?

 

Thank you.

Fowmy
Super User IV
Super User IV

@gggar 

 

Yes you can use Dataflows to do the transformations but keep only the small tables over there. If your fact tables a huge, it's not that recommend to have in Dataflow, Power Query desktop is better in this case. Any how you test it in dataflows for facttables and move them if it's not performing well. 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn   Join my Power BI User

View solution in original post

Fowmy
Super User IV
Super User IV

@gggar 

It's absolutely possible with Calculated Tables. There are many table functions to achieve this. The summary tables get updated when you load the file or when data get refreshed. You may have any level of summarization with required calculations to make your reports faster. Please refer to the following documentation for further help:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-calculated-tables


 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn   Join my Power BI User

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors