Visualizing a dynamic summarized table?

[Anonymous]

Hi all,

I have a question regarding the visualizing of data from a dynamic summarized table.

The situation: We are doing Monte Carlo Analyses on risks. This analysis simulates every risk 10.000 times to estimate the impact. The result of those simulations (the impact of the risk in Euro's) is written to our SQL Server. Power BI uses the raw data as a source, which is structured as follows:

risk_id	simulation_number	impact
1	1	1.250
1	2	1.600
1	…	...
1	9999	900
1	10000	1.000
2	1	900.000
2	2	1.000.000
2	…	...
2	9999	950.000
2	10000	980.000

The requirement is to sum the impact of all risks and group by the simulation number, so that we always end up with 10.000 rows (regardless whether 1 or 50 risks are selected).

Then, we want to divide the summed impact into bins.

Finally, we want to show how many simulations fell into each bin by using a histogram.

The issue I am currently facing is that there is also a need for filtering, for example on a project (which contains a subset of risks). When a filter is applied, Power BI should re-sum the impacts of the risks and group by simulation number. Therefore, creating a summarized table (by which I mean using DAX's SUMMARIZE function) which only refreshes when the dataset is refreshed does not meet this requirement.

My questions is therefore: is it possible to use a dynamic summarized table as a source for a histogram? 

If yes, what are the steps to achieve this? If no, are there any alternatives I could look into?

Another option I have tried is using a custom visual from the marketplace, but they ultimately lead to the same result as the native histogram. I have also tried using the Python visual, but this has a limit of 250.000 rows (25 risks).

This is an example what I'm hoping to recreate. The source of the visual below is Excel, based on 7 risks and opportunities.

I'm looking forward to hearing your suggestions.

Thanks in advance for your help!🙂

[v-shex-msft · Accepted solution]

Hi @Anonymous,

AFAIK, current power bi does not support to create dynamic calculate column/table based on filter/slicer.

Filter/slicer operated on a virtual table generated from data model tables(calculated column/table also stored on this level), you can't use the child level interaction to affect the parent which generates them.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft ,

Thank you for your reply.

As an alternative I moved the entire Monte Carlo analysis to the Python visual in Power BI, instead of extracting the results from SQL Server. This prevents the Python visual from reaching the 250.000 row limit, however it does impact the tenant performance.