Multiple reports results to one major KPI table

[matemusic]

Hi all,

for our company we made about 15 reports for different topics (Revenue, Accuracy of the supplier, Accuracy to the buyer, Stock, Production success, Labor productivity and so on...). We used KPI visuals and pinned them to the dashboard - we have overall about 30 KPI visuals on dashboard.

But those visuals are consuming too much space and they don't show all information, we want to see.

So, now we want for all those reports to have just one KPI table with all key indicators for each of the topic - sometnhing similar like this:

 Is there a way, to join key results from different reports  to one report?

We already tried to build one model with all relevant tables and use just relevant measures, but the model is becoming too big and complicated.

We tried to connect reports to Excel and then publish Excel to Power bi online, but it still doesnt work. Is there a way to get that result?

Thank you all.

[v-jiascu-msft]

Hi @matemusic,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[matemusic]

Hey, sory..

Unfortunately, my problem is not to create a table with different measures, but to get measures from several different reports / models into one common KPI table.

The final result I'm looking for is a dashboard that contains one table with multiple KPI data instead of several KPI visualizations.

For now we need to build one huge model (instead of using existing, smaller models) that is too large and too complex for maintenance and normal operation.

[v-jiascu-msft · Accepted solution]

Hi @matemusic,

I'm afraid we can't create a report from several different reports / models. You can submit an idea here: power-bi-ideas. There may be two workarounds. One is yours. The other one is using an online Excel, which isn't perfect one.

1. Install "Microsoft Power BI Publisher for Excel".

2. Connect to the report, and PowerViot the target measures.

3. Create a summary table in a new sheet.

4. Upload this workbook into OneDrive.

5. In Power Service, "Get data" get the workbook from OneDrive as an Excel Online.

6. Everytime update the excel, then reload it in the Power BI Service, the KPI table would be updated.

Best Regards!

Dale

[matemusic]

Thank you @v-jiascu-msft, that is similar solution we are looking for.

Big problem here is refreshing. Maybe we wil try to use macro in Excel, which will refresh data every day.

[v-jiascu-msft]

Hi @matemusic,

Using macro in Excel is a wonderful idea. I have encountered refreshing problem of excel several times. If you work it out, please share the code with me (and the community). I will try it too. Thank you in advanced. 

Best Regards!

Dale

[matemusic]

If we will use this method i will share the code. Currently there are questions about stability of using this method.

[v-jiascu-msft]

Hi @matemusic,

I think the only way to find out the stability is trying it in a environment similar to the production.

Best Regards!

Dale

[matemusic]

Hi @v-jiascu-msft,

i found this code on the reddit (https://www.reddit.com/r/excel/comments/3i6hf8/code_in_vba_to_automatically_save_a_file_every_15/😞

'ThisWorkbook:

Private Sub Workbook_Open()
   Application.OnTime Now + TimeValue("00:00:10"), "SaveWb"
End Sub

Module1:

Sub SaveWb()
    ThisWorkbook.Save
    Application.OnTime Now + TimeValue("00:02:00"), "SaveWb"
End Sub

Now we wil testing connected Excel to 15 Datasets over night. I will report how it will go.

[v-jiascu-msft]

Hi @matemusic,

Thank you very much for sharing. I am eager to know the result.

Best Regards!

Dale

[matemusic]

Hi @v-jiascu-msft.

I tested it. Problem is, that somethimes connection fails, so user has to click OK on warning message for each failed connection. After that user can refresh data manually with no problem (or can wait for next auto refresh). When refresh is running over night, there is no user to click OK, so refreshing process is stopped. That is a huge problem, which I dont't know how to resolve it.

[v-jiascu-msft]

Hi @matemusic,

Thanks again for sharing. I have tried to do it with Microsoft Flow. But it didn't work.

Best Regards!

Dale

[v-jiascu-msft]

Hi @matemusic,

The visual will be very complicated whatever the visual is if we put all 15 KPIs into it. There is a workaround. Please have a try.

1. Create a new table named "KPImeta" like this:

Type                  ID
QuantityKPI       1
SalesKPI             2
PurchaseKPI       3

2. Create measures for each KPI if you didn't use a measure for indicator or target.

3. Create measures of target and indicator for the final KPI table .

13KPIindicator =
IF (
    HASONEVALUE ( 'KPImeta'[ID] ),
    SWITCH (
        VALUES ( 'KPImeta'[ID] ),
        1, [11QuantityKPI],
        2, [12SalesKPI],
        3, [10PuchaseKPI]
    ),
    9999
)

17KPItarget =
IF (
    HASONEVALUE ( 'KPImeta'[ID] ),
    SWITCH (
        VALUES ( 'KPImeta'[ID] ),
        1, [14PuchaseLastyear],
        2, [15QuantityKPIlastyear],
        3, [16SalesKPI]
    ),
    9999
)

4. Create a slicer with the column of 'KPImeta'[type]. Create a KPI with the two measures. Finally, you can choose the KPI from the slicer to show up in the KPI visual.

Best Regards!

Dale