Create summary table based on the final numbers in chart

[gggar]

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:

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.

[gggar]

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]

@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. 

[gggar]

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 · Accepted solution]

@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. 

[Fowmy]

@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